Skip to main content

Biml Basics Overview

Why Start Small​

Biml replaces a lot of clicking and dragging in SSIS designer with a few lines of XML. The fastest path into the language is to build three short Biml files in order: an empty package, a connection plus a package that uses it, and a real staging package that truncates a target table and reloads it from a source table. Once those three files compile cleanly, almost everything else in Biml is a recombination of the same building blocks.

1. A Blank Package​

Open an SSIS project, right click the SSIS Packages folder, and choose Add New Biml File. The new file lives under the Miscellaneous folder and starts with the Biml root element. Add 'Packages' and 'Package' nodes and the file is buildable.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <Package Name="MyFirstPackage" ConstraintMode="Linear" ProtectionLevel="DontSaveSensitive" />
  </Packages>
</Biml>

Right click the Biml file and choose Generate SSIS Package. A new package appears in the project's package list. Opening it shows an empty design surface because no tasks have been added yet.

2. A Project Connection​

Most useful packages need at least one connection. Connection nodes can live in the same Biml file as the package or in a separate file that the package file references. Either way, the connection has to be declared above the package that uses it.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="WarehouseSource"
      ConnectionString="Data Source=localhost\SQL2014;Initial Catalog=OpsDb;Integrated Security=SSPI;Provider=SQLNCLI11.1;"
      CreateInProject="true" />
  </Connections>
  <Packages>
    <Package Name="ConnectionTest" ConstraintMode="Linear" ProtectionLevel="DontSaveSensitive">
      <Tasks>
        <ExecuteSQL Name="TestSQL" ConnectionName="WarehouseSource">
          <DirectInput>SELECT TOP 1 * FROM Person.Person</DirectInput>
        </ExecuteSQL>
      </Tasks>
    </Package>
  </Packages>
</Biml>

The 'CreateInProject="true"' attribute promotes the connection to a project level connection manager that every package in the project can share. Right clicking the file and choosing Check Biml for Errors validates the syntax and confirms that the connection can be reached. Generating the package emits both the connection manager and the Execute SQL Task.

3. A Truncate and Reload Staging Package​

The third file builds a useful package: it truncates a staging table and then copies rows from a source table into the freshly truncated target. Variables inside the package compose a fully qualified table name and a TRUNCATE statement at runtime, which keeps the schema and table name in one place.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="OperationalSource"
      ConnectionString="Data Source=localhost\SQL2014;Initial Catalog=OpsDb;Integrated Security=SSPI;Provider=SQLNCLI11.1;"
      CreateInProject="true" />
    <OleDbConnection Name="StageDb"
      ConnectionString="Data Source=localhost\SQL2014;Initial Catalog=StageDb;Integrated Security=SSPI;Provider=SQLNCLI11.1;"
      CreateInProject="true" />
  </Connections>
  <Packages>
    <Package Name="StageProduct" ConstraintMode="Linear" ProtectionLevel="DontSaveSensitive">
      <Variables>
        <Variable Name="SchemaName" DataType="String" Namespace="User">Staging</Variable>
        <Variable Name="TableName"  DataType="String" Namespace="User">Product</Variable>
        <Variable Name="QualifiedTableSchema" DataType="String" EvaluateAsExpression="true" Namespace="User">"[" + @[User::SchemaName] + "].[" + @[User::TableName] + "]"</Variable>
        <Variable Name="TruncateTableStatement" DataType="String" EvaluateAsExpression="true" Namespace="User">"TRUNCATE TABLE " + @[User::QualifiedTableSchema]</Variable>
      </Variables>
      <Tasks>
        <ExecuteSQL Name="Truncate Staging Table" ConnectionName="StageDb">
          <VariableInput VariableName="User.TruncateTableStatement" />
        </ExecuteSQL>
        <Dataflow Name="PopulateStageProduct">
          <Transformations>
            <OleDbSource Name="ProductSource" ConnectionName="OperationalSource">
              <DirectInput>
                SELECT ProductID, Name, ProductNumber, MakeFlag, FinishedGoodsFlag,
                       Color, SafetyStockLevel, ReorderPoint, StandardCost, ListPrice,
                       Size, SizeUnitMeasureCode, WeightUnitMeasureCode, Weight,
                       DaysToManufacture, ProductLine, Class, Style,
                       ProductSubcategoryID, ProductModelID,
                       SellStartDate, SellEndDate, DiscontinuedDate,
                       rowguid, ModifiedDate
                FROM Production.Product
              </DirectInput>
            </OleDbSource>
            <OleDbDestination Name="StageProduct" ConnectionName="StageDb">
              <ExternalTableOutput Table="Staging.Product" />
            </OleDbDestination>
          </Transformations>
        </Dataflow>
      </Tasks>
    </Package>
  </Packages>
</Biml>

The package has no error handling beyond what SSIS provides by default, but it demonstrates the most common shape of an ETL package: an Execute SQL Task to prepare the destination, then a data flow to move rows.

Where the Real Power Comes In​

Hand written Biml is already faster to copy and modify than a designer file. The bigger gain shows up once code nuggets enter the picture: a single Biml template can read a metadata table and emit one staging package per source table. The blank package, the connection, and the truncate and reload pattern are the foundation. Almost every BimlScript template builds on top of those three.