Converting ASCII Fixed Files

SSIS has the capability to convert a standard ASCII fixed file.  Fixed width text files are special cases of text files where the format is specified by column widths, pad character and left/right alignment.  Column widths are in units of characters. So if you have data in a text file where the first column always has exactly 10 characters, and the second column has exactly 5, the third has exactly 12 (and so on), then you have a fixed width text file.  To load a fixed file using SSIS

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.  In this example, there is a simple ASCII file. 

Code page:  1252 ANSI Latin I.  Format: Fixed width Header row delimiter: {CR}{LF}

 

 

01

 

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

As you would in DTS, measure out the proper row width and field lengths for each field.

02

 

Once you have properly configured the ‘Flat File Connection Manager’ and ‘Flat File Source’, create an ‘OLE DB Destination’ in your data flow area. 

Once you have properly configured your OLE DB connection manager, Click the ‘New’ button next to the ‘Create Table or View” drop down. Here you will see a simple create table statement.

Change the create table statement to reflect the correct name of the table you will be loading and also change the field names to the correct field names for that table.

03 

 

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.

 

04

 

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

 

05

 

It’s that easy….

 

 

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: