We receive fixed width files that are divided into sections where the section begins with a header row (starts with HD), has zero or more detail rows (starts with UD), and ends with a trailer row (starts with TR). There can be one or more sections in a file and I needed to associate each header and trailer row with the detail rows between them.

Needless to say, the layout of each type of row can vary.

My solution was to read the file as a ragged right file with only one field defined that is more than long enough to cover what we might receive.

I then count what section a row is in. This involves incrementing the section count on a header row or if the previous row was a trailer (they sometimes omit one or the other).

The headers, detail rows, and trailers are written to separate tables along with the section count.

I then run a SQL query to load the header information into the detail rows, followed by another query for the trailers.

Before loading the rows to the tables, I use substring commands to parse the row into individual fields.

You could build the substring commands in Excel easily enough and copy and paste to SSIS. Or if the file layout is already defined in a table, you could write a query to build them. The files I am currently converting to SSIS are already defined in Informatica so I could run a query against the Informatica Repository to get the fields and the substring command.

The first approach I tried involved sending the headers and trailers to a flat file which I would read back in using a connection manager where each field was listed but with a large filler at the end. Then I would load them to tables and read the original file with a connection manager where each detail field was defined. A lookup would be done to the header and trailer tables.

This proved a bad idea when I tried to process a file where the rows were shorter than the start of the last field. SSIS ignored the carriage return/line feed and appended the next row to the end of the row I was reading. So I loaded half the rows.