Complex Flat Files in SSIS

Question

SSIS is great at handling flat files where all the records are the same, but not so good when there is a little complexity.

I want to import a file similar to this -

Customer: 2344
Name:     John Smith

Item     Description       Price    Qty
543455   Widget 1           4.00      2
543556   Widget 2           8.00      1

I want to turn this into the following output -

2344, John Smith, 543455, Widget 1, 4.00, 2
2344, John Smith, 543556, Widget 2, 8.00, 1

For the life of me, I can't find a simple way to do this. Has anyone got any suggestions? Or do I need a better ETL tool?

Answer 1

The only successful way I've found to handle this kind of semi-structured input file in SSIS is to use a script task to read it line-by-line, storing the output in a text file (which is then used as a datasource further processing).

I've never had the need to try and do it in a dataflow task, which I imagine would be difficult.

Answer 2

Script task is to rescue.