Integration Services - Header and Footer

I have been struggling to get this right in Integration Services, Would really appreciate the help.

Basically I have a file with fixed length columns that I would like to import into my database using Integration Services, the file looks something like this

AN_HeaderRecord0001Testfile.txt

MyNameMySurname0127721506

MyName2 MySurname2 0127722322

FooterRec0000000004_02

What I need to find out is how do I import the data, the name and surname and telephone no, but skip the header and footer records. Also I need to be able to use the header and footer data for later use.

Any ideas ?

Rudolf,

You could do that easily in 2 steps(dataflows); the first one to add an extra column with the row number and determine the number of rows in the file (put the number in a variable using a rowcount transformation); then in the second one you can use a conditional split to re-direct the first and last row by comparing the rownumber column with the number in the variable from the first dataflow.

|||Thanx a mil for the response, I think I am getting the hang of this, will try it and let you know|||Your flat file connection manager should let you chose the option of skipping the first row altogether. Then all you'd have to worry about is removing the footer. If the detail records have a different indicator than the footer records, you can use a conditional split transformation to direct only the detail records to your destination.|||

Hi, Rapael

Could you perhaps point me in the correct direction (what components to use) to get the row number next to the row data

in the first data flow.

First Data Flow

I should have a flat file source and then a row count transformation component linked to each other ? I am having problems getting the row

data and the row number next to each other

Thanx in advance

|||

you can use an script component:

http://support.microsoft.com/kb/908460

|||Or use the Row Number transform here: http://www.sqlis.com/93.aspx