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.
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
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.
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.
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.
