Converting an ASCII variable record type/length file

From time to time I will stumble across a file that has numerous record types imbedded in the file.  The first record could contain header information, followed by a number of detail records, and a summary record.  More than likely, each record type will have a different length with different data in it.  There are a number of ways to split and load files like this. One way would be to drag and drop ‘‘Flat File Source’’ onto your work area.  Click on the newly added ‘Flat File Source’ icon to set up your connection manager. 

Once the Flat File Connection Manager Editor is open, you can configure it to properly read your Flat file.   

Code page:  1252 (ANSI – Latin I) Format: Ragged right  Header row delimiter: {CR}{LF}

Click on the columns section in your editor to set up columns for your flat file.  For fixed files, this interface will look similar to SQL Server 2000’s DTS

NOTE: There are many methods that can be used to convert Variable record type/length data in SSIS including using a ‘Script Component’ and/or a ‘Multicast’.  The method used below uses a conditional split and does not require creating script components in Visual Studio.

In the file depicted in the image below, the record type indicator starts at the 40th byte and spans two bytes.  All data before the record type indicator contains the exact same field names and properties for each record.  Therefore, this editor has the first 4 fields set up in the Source data columns (including the record type indicator) and has a 5th column that spans out to the end of the longest possible record.  This 5th column will be split out in a future step.

 02

As stated in the previous step, set the OutputColumnWidth for the 5th column (column 4) equal to (if the record lengths stay the same) or greater than the longest variable record.

In this case, the field lengths are fixed for each record type and the longest record will contain 500 bytes.

03

Now, add a ‘Conditional Split’ transformation from the data flow toolbox

04

Click on the ‘Conditional Split’ icon to open the conditional split transformation editor.

 The data in this example contains 3 types of records specified in the record type indicator [Column 3]:

Header = 11

Detail = 32

Summary = 23

Therefore, you will need to set up the transformation editor similar to the one pictured below.  This will split the data to 3 separate OLE DB Destinations. 

05

Once you have properly configured your ‘Conditional Split’ transformation, create a separate ‘Derived Column’ transformation for each record type. 

When you create the destination for the conditional split, SSIS will prompt you to enter the input you would like for each destination. 

In the below example, there are 3 ‘Derived Column’ transforms; one for each of my record types

 06

Click on the ‘Derived Column’ transformation to open the editor.  For each field after the record type indicator, create a new derived column and use the ‘Substring function’ to properly separate your fields.

 In the example below, the Header record contained 18 fields after the record type indicator:

INVOICE_DATE: starts at the 1st  byte for 6 bytes (substring[column 4], 1,6)

ORDER_NUMBER: starts at the 7th byte for 22 bytes (substring[column 4], 7,22)

PO_DC_CD: starts at the 19th byte for 1 byte (substring[column 4], 29,1)

07

Once you have properly configured the ‘Derived Column’ transforms for each record type, create an OLE DB Destination for each record type in your data flow area.  Click the ‘New’ button next to the ‘Create Table or View” drop down. Here you will see a simple create table statement.

First, remove the variable column [column 4] and rename your table and any of default columns to their desired name.

08

In the ‘Mappings’ section of your OLE DB destination editor, link up your Input columns with Destination columns by dragging and dropping the input over to the desired output creating a link.

 Your variable record should not link to anything in the Destination Column table.

09

Once you have properly set up your mapping, return to your data flow work area and click Debug (or F5).

10

Advertisements

Tags: , , , ,

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: