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