SSIS Script Component

Question

I found this piece of code which works fine for my input (a string of CSV). The package runs fine, but instead of getting 5 columns of output, I only get 2 columns. I added additional columns in the Script Component and also changed their data types to the suitable ones. The input is 643492,PV STRIP 1X1 UTILITY UP,,67.5,393446. Out of this entire string, I only get 643492 in one column and PV STRIP 1X1 UTILITY UP in another. Rest of the string comes out blank no idea why. Experts kindly help. I dont know anything about .net and I am new to SQL Server as well.

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
    Dim strRow As String
    Dim strColSeperator As String
    Dim rowValues As String()
    strRow = Row.Line.ToString()
    If strRow.Contains(",") Then
        strColSeperator = (",")
    ElseIf strRow.Contains(";") Then
        strColSeperator = ";"
    End If

    rowValues = Row.Line.Split(CChar(strColSeperator))
    Row.Code = rowValues.GetValue(0).ToString()
    Row.Description = rowValues.GetValue(1).ToString()

End Sub

Brian Knight Brian Knight · Accepted Answer · 2012-01-16T22:14:22

It appears that you are only assigning the first two values to the buffer row. The last two lines of your code snippet is where the assignment is being made. Your rowValues array should contain 5 values based the Split() on the string you provided in your example. You can assign them using the GetValue(index) method. Do this for indexes 3 through 5 of your array.

The string values go like above:

Row.FieldName = rowValues.GetValue(0).ToString()

where Code is the name of the field and 0 is the zero-based index of the value in the array.

For an integer, you can do something like:

Row.FieldName = Convert.ToInt32(rowValues.GetValue(3))

You can use other Convert methods to convert to your desired output type. See MSDN for reference.

SSIS Script Component

1 Answers