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: J开发者_JAVA百科ohn 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?
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.
Script task
is to rescue.
精彩评论