How to capture a Header or Trailer Count Value in a Flat File and Assign to a Variable

Recently I had several questions concerning how to process files that carry a header and trailer in them. Typically those files are a product of data extract from non Microsoft products e.g. Oracle database encompassing various tables data where every row starts with an identifier. For example such a file data record could look like:

HDR,INTF_01,OUT,TEST,3/9/2011 11:23

B1,121156789,DATA TEST DATA,2011-03-09 10:00:00,Y,TEST 18 10:00:44,2011-07-18 10:00:44,Y
B2,TEST DATA,2011-03-18 10:00:44,Y
B3,LEG 1 TEST DATA,TRAN TEST,N

B4,LEG 2 TEST DATA,TRAN TEST,Y

FTR,4,TEST END,3/9/2011 11:27

A developer is normally able to break the records using a Conditional Split Transformation component by employing an expression similar to

Output1 --  SUBSTRING(Output1,1,2) == "B1"

and so on, but often a verification is required after this step to check if the number of data records read corresponds to the number specified in the trailer record of the file.

This portion sometimes stumbles some people so I decided to share what I came up with.

As an aside, I want to mention that the approach I use is slightly more portable than some others I saw because I use a separate DFT that can be copied and pasted into a new SSIS package designer surface or re-used within the same package again and it can survive several trailer/footer records (!).

See how a ready DFT can look:

DFT_Composition

The first step is to create a Flat File Connection Manager and make sure you get the row split into columns like this:

FFS_Columns_Value FFSE_Look

After you are done with the Flat File connection, move onto adding an aggregate which is in use to simply assign a value to a variable (here the aggregate is used to handle the possibility of multiple footers/headers):

Aggr_Editor

The next step is adding a Script Transformation as destination that requires very little coding.

First, some variable setup:

Script_Setup

and finally the code:

Script_Code

As you can see it is important to place your code into the appropriate routine in the script, otherwise the end result may not be as expected.

As the last step you would use the regular Script Component to compare the variable value obtained from the DFT above to a package variable value obtained say via a Row Count component to determine if the file being processed has the right number of rows.

Advertisements

Posted on June 28, 2011, in Uncategorized. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: