SQLTeam.com | Weblogs | Forums

Import Flat File with Header and Trailer to SSIS OLEDB Destination

Hello,

I have been asked to create a SSIS package that imports a flat file, which does not have column names in the first row, but has a trailer and footer before and after the data. It needs to be imported into a persistent table that is re-created every day as the daily files arrive. The table does not need to have column names in the first row, but it needs to keep the header and trailer rows as the first and last rows in the table, which are already being included in the import flat file. I don't need to export them to separate tables. I just need to keep them in the beginning and end of the table. The import file is pipe delimited CRLF at the end of each row:

H|20200916|CLIENT NAME
D|AAA|123|LASTNAME|FIRSTNAME|F|2|1|20200916|20200101|
D|BBB|124|LASTNAME|FIRSTNAME|M|2|2|20200916|20200101|
T|20200916|0000024565

I am able to successfully import the file into the table, however, my rows are not lining up correctly when I query the data in the table.

Results resemble the first row being inserted as part of he header row in the table, and the trailer info does not even show up at the end.
H|20200916|CLIENT NAME D|AAA|123|LASTNAME|FIRSTNAME|F|2|1|20200916|20200101|
D|BBB|124|LASTNAME|FIRSTNAME|M|2|2|20200916|20200101|

What I am looking to achieve is to have the header and trailer rows go to their own rows in the table.
I have tried various attempts with no such luck.

Thank you,
Chris

you can read the first column and then redirect the row to separate data flows. since the header and trailer have different number of fields, it won't import as you have found out. Something like this

who is providing you this data? do you have any say in it's format?

Hi Mike,

This is very helpful. Thank you.

I followed your instructions and have been making good progress. Most of the steps worked. However, I am receiving an error on the Script task step that states the following when I execute the package:

For now, I am just creating the Header and the Trailer steps and will add the Data step later since that has more columns and rows.

image

So when I open the Edit Script window, I see that the HeaderBuffer and DetailBuffer are underlined in red.

Any ideas as to what may be wrong? This is the piece that I inserted from your directions and just changed your D to a T since I am dealing with the Trailer record

I changed the header lines to have my column names in them, but the HeaderBuffer is still in red.

Thank you.

image

In the past when I’ve had to deal with data like this I simply create a table with one column that’s long enough to accommodate the longest row. Then I create a view that filters out rows not beginning with “D”. Then I use a split function to place the data in the correct columns.

I think the problem here is the definition of the script component. You have to define the outputs in the script configuration before you can use the code.

This is why, for me, I am getting tired of ssis and moving towards scripting languages to do all ssis like processes using powershell and python.

hi yosiasz

You
moving towards scripting languages to do all ssis like processes using powershell and python.

i have done some scripting in powershell and python !!!

how are powershell and python. working out in place of SSIS
what sort of things are they making easy for you

thanks :+1: