Create new row on the fly

I have data that looks like the following

ID
=====
123456
234567
345678
456789/567890
654321/765432/876543

In some instances, the older system had to store multiple IDs in one field and used a "/" delimiter. Does anyone have any ideas on how to create new rows on the fly and split the IDs out to the new rows?

Thanks in advance.

You will need an asynchronous script component to do this. If you type that into Google you'll find loads of resources plus loads in BOL. reply here if you have any problems.

Or, if you know what the maximum number of values you will ever get in a single column are then you could use a Derived Column to split them up and an Unpivot transform to convert the values into new rows.

-Jamie

|||

Jamie Thomson wrote:

You will need an asynchronous script component to do this. If you type that into Google you'll find loads of resources plus loads in BOL. reply here if you have any problems.

I'm facing the same problem and found your article at http://blogs.conchango.com/jamiethomson/archive/2005/07/25/1841.aspx, but I'm stuck due to my ignorance of Visual Basic and the SSIS API.

If I understand correctly, all I need to do is override the Input0_ProcesssInputRow method. Here's my pseudo-code to generate multiple rows whose "code" column is populated from substrings of the "ICS" column, separated by a ",":

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

While ("," occurs in Input0Buffer.Row.ICS)

Output0Buffer.AddRow().code = substring(Input0Buffer.IC, start, length)

End While

End Sub

What does the real code to do that?

Thanks!

|||

Kevin Rodgers wrote:

Jamie Thomson wrote:

You will need an asynchronous script component to do this. If you type that into Google you'll find loads of resources plus loads in BOL. reply here if you have any problems.

I'm facing the same problem and found your article at http://blogs.conchango.com/jamiethomson/archive/2005/07/25/1841.aspx, but I'm stuck due to my ignorance of Visual Basic and the SSIS API.

If I understand correctly, all I need to do is override the Input0_ProcesssInputRow method. Here's my pseudo-code to generate multiple rows whose "code" column is populated from substrings of the "ICS" column, separated by a ",":

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

While ("," occurs in Input0Buffer.Row.ICS)

Output0Buffer.AddRow().code = substring(Input0Buffer.IC, start, length)

End While

End Sub

What does the real code to do that?

Thanks!

That won't work but the following might:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

While ("," occurs in Input0Buffer.Row.ICS)

Row.code = substring(Input0Buffer.IC, start, length)

End While

End Sub

You don't need to call AddRow() if its a synchronous component.

-Jamie

|||

Jamie Thomson wrote:

That won't work but the following might:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

While ("," occurs in Input0Buffer.Row.ICS)

Row.code = substring(Input0Buffer.IC, start, length)

End While

End Sub

You don't need to call AddRow() if its a synchronous component.

I may have more output rows than input rows, so it's an asynchronous component by definition, right?

Plus, I need the code that implements the ("," occurs in Input0Buffer.Row.ICS) pseudo-code and the substring(Input0Buffer.IC, start, length) pseudo-code.

|||

Just to follow through, here's the working code:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim index As Integer = 0

If Not (Row.CSV_IsNull) Then

For Each value As String In Row.CSV.ToString().Split(","c)

'Add a row to the output buffer:

Output0Buffer.AddRow()

'Preserve columns from the input buffer:

'Output0Buffer.key = Row.key

'Output0Buffer.CSV = Row.CSV

'Add output columns:

Output0Buffer.index = index

Output0Buffer.value = value

index += 1

Next

End If

End Sub