SSIS - Fixed width flat file mapping tool/technique

Question

I am wondering if there is a tool (or a special technique) that anyone has used to map fixed width flat files in SSIS Flat file connection managers?

Usually I just use the Flat File Connection Manager Editor (Advanced tab) and start adding the column info (name, width, datatype etc.) for all the columns 1 by 1.

This has worked fine for me in the past but now I have to map a fixed width file that has close to 500 columns...

I am thinking (hoping) that there should be a tool or technique I can use that will allow me to accomplish the mapping in a more stream lined fashion...

Using the data dictionary I was provided for the file I could easily create an excel (or text) document for example with a few columns that includes the necessary information SSIS needs (Column Name, Delimiter,InputColumnWidth, OutpuColumnWidth, DataType) to map the file.

I am wondering if there is an SSIS tool or component that can then read this file and automatically create the mappings in the Flat File Connection Manager for me...

If there is no such tool, does anyone have any tricks or tips that might be able to help me map the file in the most efficient manner?

I am wondering if maybe I can modify the SSIS package xml file in a way where I could just create some code that scripts out the column info for all the columns in xml format and then I could just manually copy and paste it into the package xml file... not sure if this would work....

Note: After mapping the file in SSIS, it will then be loaded into a SQL Server table

Answer 1

Two options come to mind, both are features of the can't-say-enough-good-things-about-this-free-tool BIDS Helper.

Create Fixed Width Columns

The first is the Create Fixed Width Columns feature. It's as simple as Create a Flat File Manager pointing at the correct file and define 1 column.

Once that's done, then you right click back on the Connection Manager and use the context sensitive feature of "Create Fixed Width Columns..."

In the next window, you paste the tab delimited list (Excel) into the editor

Oh yeah, it's that's simple. If you need to do some tweaking for column types and such, then you go in and change it as normal with edit.

Biml

I like the Business Intelligence Markup Language for my SSIS development. There's lots of benefit to it but at a most basic level, use it for things like godawful flat files, especially when they provide no header rows (I'm looking at you, you mainframes).

It has often been my experience when dealing with legacy systems, that they have their file definitions in COBOL copybooks. Those devs will send me Excel files with the column layout like

DATABASE FIELD NAME    START LOC   LENGTH
SEND.DT                 1          STRING(08)
SEND.TIME               9          STRING(08)
DT                     17          STRING(08)
TERM                   25          STRING(04)
%ZONE                  29          STRING(01)

It ain't fancy, but Excel formulas made short work of it. So I'd create 4 new columns based on the above.

Sanitized Name (removed invalid characters for column names)

=SUBSTITUTE(SUBSTITUTE(B3, ".", "_"), "%", "PCT_")

Length (extracted the numbers, note this still has a leading 0 but it doesn't hurt)

=MID(E3,LEN("STRING(")+1,LEN(E3)-LEN("STRING(")-1)

XML (this builds out the actual XML for a flat file definition)

=CONCATENATE("<Column Name=""",F3,""" Length=""",G3,"""  DataType=""AnsiString""  ColumnType=""FixedWidth""  CodePage=""1252"" />")

DDL (Might as well build the target table while I'm at it)

=CONCATENATE(",   ", F3, " varchar(", G3, ")")

The first column of the DDL gets the lead comma dropped and then wrapped with CREATE TABLE dbo.FOO()

Ultimately, that XML results in something that gets slapped into a FlatFileFormat tag and then it's a simple matter of creating the Connections collection and finally the package itself. Looks a lot but it's actually pretty simple, once you have a pattern.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">

    <FileFormats>
        <FlatFileFormat
            Name="FFF Pickup"
            CodePage="1252"
            RowDelimiter="CRLF"
            IsUnicode="false"
            FlatFileType="RaggedRight">
            <Columns>
                <Column Name="SEND_DT" Length="08"  DataType="AnsiString"  ColumnType="FixedWidth"  CodePage="1252" />
                <Column Name="SEND_TIME" Length="08"  DataType="AnsiString"  ColumnType="FixedWidth"  CodePage="1252" />
                <Column Name="DT" Length="08"  DataType="AnsiString"  ColumnType="FixedWidth"  CodePage="1252" />
                <Column Name="TERM" Length="04"  DataType="AnsiString"  ColumnType="FixedWidth"  CodePage="1252" />
                <!--
                ad nauseum
                --> 
                <Column Name="RPRTD_PU_PCS" Length="5"  DataType="AnsiString"  ColumnType="FixedWidth"  CodePage="1252" />
            </Columns>
        </FlatFileFormat>
    </FileFormats>

    <Connections>
        <FlatFileConnection
            Name="FF Pickup"
            FileFormat="FFF Pickup"
            FilePath="C:\ssisdata\Operations\Input\Pickup Report Pickups.txt"
            CreateInProject="false"
        />
    </Connections>

<Packages>
    <Package Name="PickupLoad" ConstraintMode="Linear" ProtectionLevel="DontSaveSensitive">
        <Tasks>
            <Dataflow Name="DFT Load Pickups" >
                <Transformations>
                    <FlatFileSource 
                        Name="OLE_SRC Pickup" 
                        ConnectionName="FF Pickup" 
                        RetainNulls="true">
                    </FlatFileSource>
                </Transformations>
            </Dataflow>
        </Tasks>
    </Package>
</Packages>

Oh and a similar question from SO with a disturbingly similar answer. Wish I had looked at Skinner's comments before I rewrote this answer.