Feb 7, 2013

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:
111
222
333
444

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:
111
22
333
44

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

Continue reading here @ BeyondRelational.com.