Archives

Simplifying Imported Biml

This post is part of a series, Short Cuts to Biml Mastery which shows you how to learn BimlScript through a series of short cuts and remain productive while doing so. In the previous article, I covered how to import existing packages into Biml using BimlExpress. In this article, we’ll examine that Biml and simplify it in preparation for automation. If you’ve been reading along in this series, you’ll remember that we started with a single SSIS package which extracts data from a SQL source and creates a CSV file with quote identifiers. The generated file looks like the following:

"OrderID","CustomerID","EmployeeID","OrderDate","RequiredDate","ShippedDate","ShipVia","Freight","ShipName","ShipAddress","ShipCity","ShipRegion","ShipPostalCode","ShipCountry"
"10262","","8","1996-07-22 00:00:00","1996-08-19 00:00:00","1996-07-25 00:00:00","3","48.29","Rattlesnake Canyon Grocery","2817 Milton Dr.","Albuquerque","NM","87110","USA"
"10317","","6","1996-09-30 00:00:00","1996-10-28 00:00:00","1996-10-10 00:00:00","1","12.69","Lonesome Pine Restaurant","89 Chiaroscuro Rd.","Portland","OR","97219","USA"

The raw imported Biml is below:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <Connection Name="connNorthwind" ConnectionString="provider=SQLNCLI11;data source=localhost;integrated security=SSPI;initial catalog=Northwind" />
        <FlatFileConnection Name="conn_ff_Orders" FilePath="C:\FileExport\Orders.csv" FileFormat="conn_ff_Orders" />
    </Connections>
    <Packages>
        <Package Name="Export Orders" Language="None" ConstraintMode="LinearOnCompletion" ProtectionLevel="DontSaveSensitive">
            <Tasks>
                <Dataflow Name="DFT Load file">
                    <Transformations>
                        <OleDbSource Name="OLE_SRC Orders" ConnectionName="connNorthwind">
                            <DirectInput>
								SELECT * FROM dbo.Orders
                            </DirectInput>
                        </OleDbSource>
                        <FlatFileDestination Name="FF_DEST Orders" ValidateExternalMetadata="false" LocaleId="None" ConnectionName="conn_ff_Orders">
                            <Header></Header>
                        </FlatFileDestination>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
    <FileFormats>
        <FlatFileFormat Name="conn_ff_Orders" CodePage="1252" TextQualifier="_x0022_" ColumnNamesInFirstDataRow="true" RowDelimiter="">
            <Columns>
                <Column Name="OrderID" Delimiter="Comma" />
                <Column Name="CustomerID" Length="5" DataType="String" Delimiter="Comma" MaximumWidth="5" />
                <Column Name="EmployeeID" Delimiter="Comma" />
                <Column Name="OrderDate" DataType="DateTime" Delimiter="Comma" />
                <Column Name="RequiredDate" DataType="DateTime" Delimiter="Comma" />
                <Column Name="ShippedDate" DataType="DateTime" Delimiter="Comma" />
                <Column Name="ShipVia" Delimiter="Comma" />
                <Column Name="Freight" DataType="Currency" Delimiter="Comma" />
                <Column Name="ShipName" Length="40" DataType="String" Delimiter="Comma" MaximumWidth="40" />
                <Column Name="ShipAddress" Length="60" DataType="String" Delimiter="Comma" MaximumWidth="60" />
                <Column Name="ShipCity" Length="15" DataType="String" Delimiter="Comma" MaximumWidth="15" />
                <Column Name="ShipRegion" Length="15" DataType="String" Delimiter="Comma" MaximumWidth="15" />
                <Column Name="ShipPostalCode" Length="10" DataType="String" Delimiter="Comma" MaximumWidth="10" />
                <Column Name="ShipCountry" Length="15" DataType="String" Delimiter="CRLF" MaximumWidth="15" />
            </Columns>
        </FlatFileFormat>
    </FileFormats>
</Biml>

When first learning Biml, you should examine the Biml code and the SSIS package side by side. The first section (lines 2 through 5) covers the connections, one of which is an OLE DB connection to the Northwind database. The second connection is involved with the flat file export.

The second section (lines 6 through 23) describe the package and is fairly self explanatory. A single package, composed of a single data flow task, containing only the OLE DB source and flat file destination.

The third section (lines 24 through 43) is a bit more complicated. As shown previously, flat file connections require more configuration. Compare what you see in this section to these screenshots from a previous article.

The connection manager general tab configuration corresponds to line 25 from the code above. The most confusing thing would be the value “x0022” for TextQualifier, which is the hexadecimal value for double quotes. The double quotes is a reserved XML character, so we’ll substitute the following: &quot;

The rest of the FlatFileFormat describes the column information which corresponds to this:

So, when you compare the Biml to the SSIS GUI, everything starts to make sense. The columns typically specify the Biml data type, length and/or maximum width, and delimiter character. Notice that every column is “Comma” delimited except the last which specifies “CRLF”, carriage return line feed. These settings match the SSIS package flat file manager editor advanced tab.

Remember that our ultimate goal is to be able to loop through a specified set of queries (or tables, views, etc.) and dynamically create an export package for each. While the Northwind connection is shared by every export package, it can only be specified once. So, we’ll move that connection to it’s own file, Environment.biml.

<!-- Environment.biml -->
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
		<Connection Name="connNorthwind" ConnectionString="provider=SQLNCLI11;data source=localhost;integrated security=SSPI;initial catalog=Northwind" />
    </Connections>	
</Biml>

The last section, the FlatFileFormat, is the most involved because it requires column metadata which is very dynamic. I’ve made the following changes to the FlatFileFormat biml to make it more consistent and easier to automate.

  • Replaced the TextQualifier value mentioned above.
  • Removed the MaximumWidth attribute as it is not necessary.
  • Added the Length attribute to every column, even though it’s only required for character data types. A value of 0 is ignored anyway.

After our cleanup, the Export Orders Pkg.biml looks like this.

<!-- Export Orders Pkg.biml -->
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <FlatFileConnection Name="conn_ff_Orders" FilePath="C:\FileExport\Orders.csv" FileFormat="fff_Orders"/>
    </Connections>
    <Packages>
        <Package Name="Export Orders" ConstraintMode="Linear" ProtectionLevel="DontSaveSensitive" PackageSubpath="Export Flat Files">
            <Tasks>
                <Dataflow Name="DFT Load file" >
                    <Transformations>
                        <OleDbSource Name="OLE_SRC Orders" ConnectionName="connNorthwind">
                            <DirectInput>
SELECT * FROM dbo.Orders
                            </DirectInput>
                        </OleDbSource>
                        <FlatFileDestination Name="FF_DEST Orders"  ConnectionName="conn_ff_Orders" Overwrite="true" ValidateExternalMetadata="false"/>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
    <FileFormats>
        <FlatFileFormat Name="fff_Orders" FlatFileType="Delimited"  ColumnNamesInFirstDataRow="true" TextQualifier=""">
            <Columns>
                <Column Name="OrderID" DataType="Int32" Length="0" Delimiter="Comma"/> 
                <Column Name="CustomerID" DataType="StringFixedLength" Length="5" Delimiter="Comma"/> 
                <Column Name="EmployeeID" DataType="Int32" Length="0" Delimiter="Comma"/> 
                <Column Name="OrderDate" DataType="DateTime" Length="0" Delimiter="Comma"/> 
                <Column Name="RequiredDate" DataType="DateTime" Length="0" Delimiter="Comma"/> 
                <Column Name="ShippedDate" DataType="DateTime" Length="0" Delimiter="Comma"/> 
                <Column Name="ShipVia" DataType="Int32" Length="0" Delimiter="Comma"/> 
                <Column Name="Freight" DataType="Currency" Length="0" Delimiter="Comma"/> 
                <Column Name="ShipName" DataType="String" Length="40" Delimiter="Comma"/> 
                <Column Name="ShipAddress" DataType="String" Length="60" Delimiter="Comma"/> 
                <Column Name="ShipCity" DataType="String" Length="15" Delimiter="Comma"/> 
                <Column Name="ShipRegion" DataType="String" Length="15" Delimiter="Comma"/> 
                <Column Name="ShipPostalCode" DataType="String" Length="10" Delimiter="Comma"/> 
                <Column Name="ShipCountry" DataType="String" Length="15" Delimiter="CRLF"/> 
            </Columns>
        </FlatFileFormat>
    </FileFormats>
</Biml>

So, the Biml is easier to read now, but how are we going to automate it? Well, the ultimate solution is to use either C# or VB.NET code nuggets to loop through the entire file and create each package. This qualifies as BimlScript. However, this series is about short cuts which involve string concatenation. It’s faster and easier (for some), and I call this method Scripting Biml. In the next article, we’ll use Excel to generate our Biml dynamically, which will be our first productivity improvement.

The Export Orders Pkg and Environment Biml files we just created can be downloaded here.

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>