How to merge rows from fixed delimited text file into one SQL table row with SSIS?

Question

Hei, I am writing an SSIS package to run on SQL Server 2008, importing fixed delimited text file. The file contain several rows per one user. How to merge the (connected) rows from the file into respectively one row in database table. I have a fixed-delimited file which looks something like that:

1000001 5555557777AAA000000000000…..

1000001 5555557777BBB000000000000…..

1000002 5555557777AAA000000000000…..

1000002 5555557777BBB000000000000…..

1000002 5555557777CCC000000000000…..

1000003 5555557777AAA000000000000…..

1000003 5555557777FFF000000000000…..

1000003 5555557777DDD000000000000…..

I would like to insert it into database table where the rows for particular ID (1000001, 1000002, 1000003, ect.) will become one row:

1000001 5555557777AAA000000000000…..1000001 5555557777BBB000000000000…..

1000002 5555557777AAA000000000000…..5555557777BBB000000000000…..5555557777CCC000000000000…..

1000003 5555557777AAA000000000000…..5555557777FFF000000000000…..5555557777DDD000000000000…..

What will be the recommended strategy with SSIS ?

Answer 1

Normally I'd say use the Pivot transformation but that's not going to work with your scenario as you want to preserve the original line in its entirety.

The Pivot transformation would make 1000001 5555557777AAA000000000000…..

1000001 5555557777BBB000000000000…..

look more like (note the rollup key 1000001 is only listed once)

1000001 5555557777AAA000000000000….. 5555557777BBB000000000000

I would say will be best served by using an asynchronous script transformation to accomplish this.

Top search hits for doing this

• Creating an Asynchronous Transformation with the Script Component http://msdn.microsoft.com/en-us/library/ms136133.aspx
• SSIS Synchronous/Asynchronous Script Transformations in a Data Flow http://bi-tch.blogspot.com/2008/02/ssis-synchronousasynchronous-script.html
• How to create an Asynchronous Script Component in SSIS?