3

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?

Adrian
  • 6,013
  • 10
  • 47
  • 68
Craig Schwarze
  • 11,367
  • 15
  • 60
  • 80
  • 1
    While Ed has a good idea, I would strongly suggest that you at least try to get a better formatted file from whoever is providing it. I know it's not always possible, but I alaways try to get something in the structure I prefer and, 90+ percent of the time, they will accomodate me. – HLGEM Dec 10 '09 at 18:36

2 Answers2

5

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.

Ed Harper
  • 21,127
  • 4
  • 54
  • 80
  • +1 This is the only way that I would know how to solve this problem, too. – rfonn Dec 10 '09 at 14:23
  • 3
    I have one thing to add, instead of using a script task, one could use a Script Component source in dataflow task. This would eliminate the need for the staging file. – unclepaul84 Dec 10 '09 at 15:27
  • That is a good idea because then you could use the outputted buffer from the script component and do any massaging of the data before you eventually land it – rfonn Dec 10 '09 at 17:56
1

Script task is to rescue.

deeps_rule
  • 339
  • 2
  • 7
  • 14