Converting a string with multiple lines into multiple rows with 1 line

[smn198]

Hello!

I've just picking up SSIS but I've run into a problem where I want to do some non-standard string manipulation. I've got a data source with 2 columns. ID and data. The data column is a string but has multiple lines within it seperated by return characters. I would like each line of data to have its own ID with the corrosponding ID.

It currently looks like this:

[tt]ID Data
==========
1 Jack
Jane
Jill
----------
2 Adam
Anita
Anne
----------
3 Bill
Betty[/tt]

And I want it to look like this:
[tt]ID Data
==========
1 Jack
----------
1 Jane
----------
1 Jill
----------
2 Adam
----------
2 Anita
----------
2 Anne
----------
3 Bill
----------
3 Betty[/tt]

I think I need to use a script transform but I'm not really sure how to go about doing that. Any ideas?

 

[katbear]

Yes, you will need to do some scripting. The msdn forums have some really good mvp's that can point you in the right direction:

http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1

Meanwhile, here is an example of some data manipulation. Not exactly what you're looking for, but educational:

http://agilebi.com/cs/blogs/jwelch/...at-files-with-varying-numbers-of-columns.aspx