BIML – Importing Text Files


Here’s a BIML snippet for reading data from a text file.  If you’ve read the prior post about pushing data (from a table) to a text file, you’ll see a lot of the same constructs.  There is;

  1. A connection object (ie the FlatFileConnection) that specifies a path to a file.  The connection requires a FileFormat.
  2. A FileFormat which specifies the nature of the file (ie columns, data types, delimiters etc)
  3. The data flow object (ie the Source in the DataFlow which is a FlatFileSource).

I do not think there is too much complexity in the Connection or [DataFlow] Source.  There are really only a few attributes that need to be defined and, if you think about the nature of the task we are trying to achieve, there are all required.

What is the most interesting is the specification of the FlatFileFormat.  This defines the nature of the data that we are connecting to.  Both at a header and detail level.  For example consider the delimiter constructs for the header (HeaderRowDelimiter).  You would think that the delimiter is applicable to the entire file but this is not the case and it also needs to be specified on a column by column basis.  Finally, we also need to specify the new line on the last column.  Perhaps that’s not so intuitive since we specify the row delimiter in the header section of the Format.

So here is what we are trying to import.

image

And here is the BIML snippet to import it.


<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <FlatFileConnection Name="my_import" FileFormat="my_values" FilePath="C:\temp\my_values.csv" />
    </Connections>
    <FileFormats>
        <FlatFileFormat Name="my_values" ColumnNamesInFirstDataRow="true" RowDelimiter="CRLF" HeaderRowDelimiter="Comma" CodePage="1252" IsUnicode="false">
            <Columns>
                <Column Name="ID" DataType="Int32" Delimiter="Comma" />
                <Column Name="Name" DataType="AnsiString" Length="255" Delimiter="Comma" />
                <Column Name="Value" DataType="Double" Delimiter="CRLF"  />
            </Columns>
        </FlatFileFormat>
    </FileFormats>
    <Packages>
        <Package Name="import_MyValue" ConstraintMode="Linear" ProtectionLevel="EncryptAllWithUserKey">
            <Tasks>
                <Dataflow Name="load_values">
                    <Transformations>
                        <FlatFileSource Name="src_values" ConnectionName="my_import" />
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>
Advertisements

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