Skip to main content

Importing a Text File with Biml

Why This Pattern Matters​

Reading a delimited text file in SSIS always involves three coordinated objects: a flat file connection that points at the file, a flat file format that describes the file's columns and delimiters, and a flat file source inside a data flow. Biml expresses each as a node in the same XML document, which keeps the file shape, the connection, and the data flow in one place.

How the Pieces Fit​

Three Biml elements cover the entire pattern:

  1. 'FlatFileConnection' points at the physical file and references a 'FlatFileFormat' by name.
  2. 'FlatFileFormat' describes the file's row delimiter, header delimiter, code page, and the columns it contains.
  3. 'FlatFileSource' inside a 'Dataflow' reads the file using the connection.

The format definition is where the detail lives. Each column needs its own delimiter even though the row delimiter is set once on the format itself. The last column carries 'CRLF' as its delimiter to mark the end of a row.

The Source File​

A small CSV with three columns: an integer 'ItemID', a 255 character 'ItemName', and a numeric 'ItemPrice', terminated with CRLF.

The Biml​

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <FlatFileConnection Name="InventoryImport" FileFormat="InventoryFormat"
                        FilePath="C:\inbound\inventory.csv" />
  </Connections>
  <FileFormats>
    <FlatFileFormat Name="InventoryFormat" ColumnNamesInFirstDataRow="true"
                    RowDelimiter="CRLF" HeaderRowDelimiter="Comma"
                    CodePage="1252" IsUnicode="false">
      <Columns>
        <Column Name="ItemID"    DataType="Int32"      Delimiter="Comma" />
        <Column Name="ItemName"  DataType="AnsiString" Length="255" Delimiter="Comma" />
        <Column Name="ItemPrice" DataType="Double"     Delimiter="CRLF" />
      </Columns>
    </FlatFileFormat>
  </FileFormats>
  <Packages>
    <Package Name="ImportInventory" ConstraintMode="Linear"
             ProtectionLevel="EncryptAllWithUserKey">
      <Tasks>
        <Dataflow Name="LoadInventory">
          <Transformations>
            <FlatFileSource Name="SrcInventory" ConnectionName="InventoryImport" />
          </Transformations>
        </Dataflow>
      </Tasks>
    </Package>
  </Packages>
</Biml>

The connection node carries no column information. The format node owns the column metadata, including the per column delimiter that lets the parser tell columns apart inside a row. The data flow only needs the connection name; the source picks up the column definitions through the connection's reference to the format.

Adding a destination is a one line change: drop an 'OleDbDestination' or another sink under 'Transformations' and wire its 'InputPath' to 'SrcInventory.Output'. Everything else about the file definition stays the same.