transform data file based on field value
Hi,
I have a tab delimited flat file that has records grouped by batch. Each batch 'section' has a header and then multiple records for that batch. I need to look at a field in the header section of each batch and based on the value, either load
the records from that batch into a table or skip the batch entirely.
Here is an example of the file:
D493 03 6 012511CL08RILABO0LAB053
48000152246203 01241172735640 00001
48000152246203 01241172737828 00001
48000152246278 01241176029321 00001
D493 03 6 012511CL08RILABO0PSY053
48000152246203 01241172735640 00001
48000152246203 01241172737828 00001
48000152246278 01241176029321 00001
D493 03 6 012511CL08RILABO0LAB053
48000152246203 01241172735640 00001
48000152246203 01241172737828 00001
48000152246278 01241176029321 00001
So the first line will be the header of the first batch. I need to look at the 4th column, position 13-15 and if it is 'LAB' then load the records that follow until the next header section. Then check the 4th field again for 'LAB' and either
load the records that follow or skip them.
So in the example above, i would need to load the first section, skip the second, and load the third.
Is there a way to do it using SSIS?
Thanks
Scott
January 29th, 2011 7:48pm
You can follow this post to make this happen:
http://agilebi.com/jwelch/2007/07/12/processing-a-flat-file-with-header-and-detail-rows/Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
January 29th, 2011 8:53pm