March 17, 2009 at 8:56 am
Hi guys/gals,
I am trying to read a hierarchical text file using SSIS , and the text file looks as follows:
HD000010000220090311080000
PE1234567891120090318
PI00112345
PI00212346
PI00312347
PI00412348
PI00512349
PI00612350
PI00712351
PI00812352
PI00912353
PI01012354
PE2234567891120090318
PI00112355
PI00212356
PI00312357
PI00412358
PI00512359
PI00612360
PI00712361
PI00812362
PI00912363
PI01012364
TR000000000000024
And push this chunk into database. To explain the above format, we have a header record and a trailer record and lots of detail records that needs to be interpreted differently and stored into different tables.
But, the problem which people have pointed out to me is that SSIS can be configured to read delimited text files only and not hierarchical ones like the one mentioned above. One idea that struck me was to write a small converter tool that will convert this format into delimited format and use that file as the input to SSIS. But, the text file could be as big as 5GB. And I would like to know whether the approach which I have proposed will be the best solution or do we have a simpler, nicer way to accomplish the same task.
Thanks in anticipation,
G3
March 17, 2009 at 9:01 am
Is the file set up so that the lines that begin with PI belong to the preceding line that begins with PE? Is that what you mean by hierarchical in this case?
If so, it might actually be best to loop through the file using something like the VB file reader, and parse it out that way. You could either do this in SSIS with a script object, or in the database with a CLR function, or through a separate DLL file.
That would be my first thought on it. It's sequential data, which relational tools are generally poor at, because row-sequence isn't supposed to matter to them.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply