A question came up today on the SSIS forum about processing flat files that included header and detail information. The poster wanted to send header information to one destination and detail information to another. The header rows have 2 columns, the detail rows have 5.
CUSTOMER|ABC
COST|1000
ITEMS
C1|12345|123|123|123
C2|12345|123|123|123
C3|12345|123|123|123
C4|12345|123|123|123
One way to solve this is a script component, which would have two outputs, and redirect rows based on programatically determining what type of row was being dealt with. The advantages for this approach are:
- You only read the source once
- Completely flexible for whatever logic you want to use to identify header and detail rows
- It handles files that have repeated header / detail sections
A downside is that you will probably have to treat each row from the source as a single column, and break the columns out yourself. Project Real has a great example of this approach (though they show it in custom components, the same concepts apply), so rather than try to cover it again here, I’ll provide a link to their documentation. http://msdn2.microsoft.com/en-us/library/aa964134.aspx
An alternative approach is to read the same flat file twice, once to get header rows, once to get the detail. This works well when you will only have one header, detail combination per input file.
To do this for the file defined above, you need to create two connections to the flat file, one for the header defining two columns, the other for detail with five columns. For the detail connection, set the header rows to skip to 3. The detail source can be handled normally, as no special handling is required.
The header source flow does need a few additions. First, add a script component. In the script component, add a new column to the output (I named mine Rows) to hold the row count. Use the following code to add the row count:
Static RowCount As Integer = 0
RowCount = RowCount + 1
Row.Rows = RowCount
Next, add a conditional split component. Add a case for everything with a Rows value less than 2 (Rows <= 2) to only get the two header values we want. Now this flow can be written to the appropriate destination.
Rather than using a simple row count, you could use the script component to do pattern matching using Like, or other techniques to identify the header rows, and output a simple boolean flag indicating whether the row should be included.
I’ve attached a simple sample package that demonstrates this. It uses the Konesans Trash Destination Adapter (http://www.sqlis.com/56.aspx), so you’ll need that to run the sample.