SSIS Script Component Transformation

I have a table that has a key field and one field with a  line of | pipe delimited data like this:

KeyField              Line
1                          20120828|20|20.500|CLO|ISYS|LFCT|LPMCO-LP2

I have a script component that breaks this down as follows:
1         20120828
1         20
1         20.500
1         CLO
1         ISYS
1         LFCT
1         LPMCO-LP2

What I want is a horizontal row, not a vertical one.

ID    FIELD1          FIELD2     FIELD3    FIELD4   FIELD5  FIELD6   FIELD7
1      20120828    20             20.500    CLO        ISYS      LFCT      LPMCO-LP2

Is there something that I can use in Script Component? Right now it does an .AddRow.
Who is Participating?
SharonBernalConnect With a Mentor Author Commented:
I found a way to get this to work using VB.
Rainer JeschorCommented:
a script transformation can do this but you have to do some configuration (as the default settings would not allow this).
Just configure your Output (Script Transformation Editor -> Inputs and Outputs) and set "SynchronousInputID" to "None" and add columns to your output like "KeyFieldOut", "Field1","Field2" ...

Then you can simply use this in your script:
public override void Input0_ProcessInputRow(Input0Buffer Row)
        string[] separator = { "|" };
        string[] splittedLine = Row.Line.Split(separator, StringSplitOptions.None);
        OutputSplittedBuffer.KeyFieldOut = Row.KeyField;
        OutputSplittedBuffer.Field1 = splittedLine[0];
        OutputSplittedBuffer.Field2 = splittedLine[1];

Open in new window

SharonBernalAuthor Commented:
I'm not sure how to implement your code into my script. My script is below:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        Dim keyField As Integer = Row.KeyField
        Dim itemList As String = Row.Line
        Dim delimiter As String = "|"

        If Not (String.IsNullOrEmpty(itemList)) Then

            Dim inputListArray() As String = _
                itemList.Split(New String() {delimiter}, _

            For Each item As String In inputListArray
                With Output0Buffer
                    .KeyField = keyField
                    .ClaimNumber = item
                End With

        End If

    End Sub
SharonBernalAuthor Commented:
I have found an alternative solution.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.