Complex flat files in SSIS

Question

SSIS does a great job with flat files, where all entries are the same, but not very good when there is little complexity.

I want to import a file like 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 conclusion -

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

In my life I cannot find an easy way to do this. Anyone have any suggestions? Or do I need a better ETL tool?

Answer 1

The only successful way I found a handler for this type of semi-structured input file in SSIS is to use a script task to read it in turn, storing the output in a text file (which is then used as additional data processing).

, , , .

Answer 2

Script task - .