SSIS - Split single row to Multiple rows

Its been a long long time since I have blogged. So have I been away from forums hence no ideas to blog. I was back at forum today and got a nice scenario. How do you split a single row to multiple rows? The input file has only one row.

There can be 2 scenarios which arise:

Scenario 1. the number of columns expected for each output row are the same.

Input: 1*1*1~2*2*2~3*3*3~4*4*4

Treat “~” as the row delimiter and * as the pipe delimiter

Expected Output:

1	1	1
2	2	2
3	3	3
4	4	4

Scenario 2. The number of rows for each output row are different. We need to default values if we have less than expected columns and ignore the extra columns.

Input:

1*1*1*1~2*2~3*3*3*3*3*3*3~4*4

Treat “~” as the row delimiter and * as the pipe delimiter

Expected Output:

1	1	1
2	2
3	3	3
4	4

Notice the blanks in the 3rd column for 2nd and 4th row.

Continue reading here @ BeyondRelational.com.