script component

i have 2 source columns testsource and testsource1 and 2 output columns

test and test2

i am passing them through a script component to check if the columns are numeric or not along with some more logic...I am able to get the first column evaluated based on the logic but not the second column

Is it that the script component can only look at one column?

this is the code i wrote

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

If IsNumeric(Row.testsource) Then

Row.test = Left(Row.testsource, 4) + CStr("/") + Mid(Row.testsource, 5, 2) + CStr("/") + Right(Row.testsource, 2)

Else

If Row.testsource = "N.A" Then

Row.test = "NULL"

End If

Row.test = "NULL"

End If

If IsNumeric(Row.testsource1) Then

Row.test2 = Left(Row.testsource1, 4) + CStr("/") + Mid(Row.testsource1, 5, 2) + CStr("/") + Right(Row.testsource1, 2)

Else

If Row.testsource1 = "N.A" Then

Row.test2 = "NULL"

End If

Row.test2 = "NULL"

End If

End Sub

End Class

example of run

INPUT

,20070930
,20080331
,20070930
,20071130
,20070930
,
,
,
20070504,20070503
20080331,
,20070930
N.A. ,N.A.
N.A. ,N.A.

OUTPUT

NULL,20070930
NULL,20080331
NULL,20070930
NULL,20071130
NULL,20070930
NULL,
NULL,
NULL,
2007/05/04,20070503
2008/03/31,
NULL,20070930
NULL,N.A.
NULL,N.A.

as you can see the first column gets evaluated based on logic but the second column just gets passed through..

Thanks for any help in advance

smathew

Put a data viewer in the path right before the script component and ensure that you have the correct results that you expect (for instance, you don't have that comma in there). Also, you might want to add a derived column before the script component to trim() each field to get rid of any extra spaces.|||

i do have a derived column that does the trim, but it still does not evaluate the second column based on the logic..

smathew

|||

smathew wrote:

i do have a derived column that does the trim, but it still does not evaluate the second column based on the logic..

smathew

The other thing you can do after you've looked at the data viewer, is to add a MsgBox(Row.testcolumn1.ToString) to see what happens.