SSIS multiple Header and footer flat file processing

• ashishkumarrai

  SSC-Addicted

  Points: 489

  More actions

  October 18, 2008 at 7:44 am

  #123949

  Hi,

  I need to process one flat file having multiple header and footer, following structure.

  HDR1

  detail line 1

  detail line 2

  TRL1

  HDR2

  detail line 3

  detail line 4

  TRL2

  .

  .

  .

  and so on.

  If it was a single header and footer, I can store the header and footer value in a table and get rid of it, and store the detail value along with the header id in another table. But in this case, my problem is if i store these header and footer in one table and their respective detail in another table, there is no mechanism to locate the relation between the headers and their respective details data. As it will dump all the detail data into a table loosing their header and footer info. Is there any way to exclude header and footer while keeping some kind of reference to identify which header they belong to.

  As the file is bulky, I dont want to use script task to go through each and every line. Any suggestions?

  Thanks

  Ashish

• COZYROC

  One Orange Chip

  Points: 28499

  More actions

  October 24, 2008 at 3:07 pm

  #889499

  Check DataDefractor[/url]. I think it will help you handle such format.

  ---
  SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  

• COZYROC

  One Orange Chip

  Points: 28499

  More actions

  October 24, 2008 at 3:09 pm

  #889503

  Btw you may also check the EDI Source component. This format seems very similar to an EDI file.

  ---
  SSIS Tasks Components Scripts Services | http://www.cozyroc.com/