Search code examples
ssisflat-file

How to manage giant fixed-width file in SSIS?

I have a fixed width file that is about 1200 characters wide and has about 300+ columns. I'm looking for a way to create a fixed-width data source in SSIS without using the UI for the flat file connection manager. Is there a way to modify the column definitions without having to use the UI in SSIS? I can't find a file for the data connection anywhere in the project.

Am I doomed to manually add 300+ columns into the flat-file connection manager one by one?

Solution

  • Two options come to mind. The first is to Install BIDSHelper and use the Create Fixed Width Columns

    The other, as @ElectricLlama mentioned is to use BIML. This too will require the installation of BIDS Helper but to convert a .biml file into a .dtsx Short Walkthrough

    This should approximate creating a package with a flat file connection manager (with a single column) adding a data flow and inside that consume our flat file and wire it up to a Row count. This is approximate for what you want. Just fill in the XML in the Columns tag.

    <Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>
    <FlatFileConnection
        Name="FF dchess"
        FileFormat="FFF dchess"
        FilePath="C:\ssisdata\SO\Input\dchess.txt"
    />
</Connections>
<FileFormats>
    <FlatFileFormat
Name="FFF dchess"
CodePage="1252"
RowDelimiter="CRLF"
IsUnicode="false"
FlatFileType="RaggedRight"
        >
        <Columns>
            <Column Name="MyColumn" Length="08"  DataType="AnsiString"  ColumnType="FixedWidth"  CodePage="1252" />
        </Columns>
    </FlatFileFormat>
</FileFormats>
<Packages>
    <Package Name="dchess" ConstraintMode="Linear" ProtectionLevel="DontSaveSensitive">
        <Connections >
            <Connection ConnectionName="FF dchess" />
        </Connections>
        <Variables>
            <Variable Name="CurrentFileName" DataType="String">C:\ssisdata\so\Input\dchess.txt</Variable>
            <Variable Name="RowCountInsert" DataType="Int32">0</Variable>
        </Variables>
        <Tasks>
            <Dataflow Name="DFT Load file" >
                <Transformations>
                    <FlatFileSource
                        Name="FF_SRC dchess"
                        ConnectionName="FF dchess"
                        RetainNulls="true">
                    </FlatFileSource>
                    <RowCount Name="CNT Source" VariableName="User.RowCountInsert"></RowCount>
                </Transformations>
            </Dataflow>
        </Tasks>
    </Package>
</Packages>
</Biml>

    Generated package looks like

    enter image description here

    Feel free to pick your jaw up off the ground ;)