Skip to main content

Getting Started With Biml

Why Generate Packages With Markup​

Building SSIS packages by hand in the visual designer works but suffers from two problems: reproducing the same shape across many packages is manual, and renaming or restructuring after the fact means editing every package. The underlying .dtsx file is XML, but it is delicate enough that direct edits often break the package when reopened in the designer.

Biml is a separate XML specification for declaring SSIS objects. Compiling a Biml file produces clean, valid SSIS packages every time. Changing a property and regenerating produces the same package with the change applied, with no stale state in between.

Tooling​

Biml is available through BimlExpress for SQL Server Data Tools and through BimlStudio for standalone development. With either installed, add a new Biml file to a project. The file appears in the Miscellaneous folder. A single Biml file can produce many packages from one source document.

A Minimal Biml Document​

A Biml file always starts with the root element and the schema namespace:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
</Biml>

To produce a package, add a 'Packages' container with a 'Package' child. The IntelliSense lists which children are valid at any point in the document:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <Package></Package>
  </Packages>
</Biml>

Validating Before Generating​

Right-clicking the Biml file and choosing Check Biml for Errors validates the document. The first attempt above fails with two errors:

  • Every package needs a 'Name' attribute
  • Every package needs a 'ConstraintMode' attribute

The 'ConstraintMode' attribute has two valid values, Linear and Parallel. Linear creates precedence constraints between tasks in the order they appear in the source. Parallel adds the tasks side by side without any constraints. The fixed minimal document looks like this:

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

Right-clicking the Biml file and choosing Generate SSIS Packages produces an empty package under the SSIS Packages node.

Adding an Execute SQL Task​

Tasks live inside a 'Tasks' element. An Execute SQL task needs a name, a connection, and a command. Connections live at the root of the document, which lets multiple tasks and multiple packages share the same connection by name:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <Connection Name="SQL01" ConnectionString="Provider=SQLNCLI11;Data Source=.;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=OperationsDW" />
  </Connections>
  <Packages>
    <Package ConstraintMode="Linear" Name="package_01">
      <Tasks>
        <ExecuteSQL Name="fire_proc_01" ConnectionName="SQL01">
          <DirectInput>Select 1</DirectInput>
        </ExecuteSQL>
      </Tasks>
    </Package>
  </Packages>
</Biml>

How ConstraintMode Affects Multiple Tasks​

Adding a second task makes the constraint mode visible. With 'Linear', the second task waits for the first. With 'Parallel', both tasks start together:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <Connection Name="SQL01" ConnectionString="Provider=SQLNCLI11;..." />
  </Connections>
  <Packages>
    <Package ConstraintMode="Linear" Name="package_01">
      <Tasks>
        <ExecuteSQL Name="SQL_CMD_1" ConnectionName="SQL01">
          <DirectInput>Select 1</DirectInput>
        </ExecuteSQL>
        <ExecuteSQL Name="SQL_CMD_2" ConnectionName="SQL01">
          <DirectInput>Select 1</DirectInput>
        </ExecuteSQL>
      </Tasks>
    </Package>
    <Package ConstraintMode="Parallel" Name="package_02">
      <Tasks>
        <ExecuteSQL Name="SQL_CMD_1" ConnectionName="SQL01">
          <DirectInput>Select 1</DirectInput>
        </ExecuteSQL>
        <ExecuteSQL Name="SQL_CMD_2" ConnectionName="SQL01">
          <DirectInput>Select 1</DirectInput>
        </ExecuteSQL>
      </Tasks>
    </Package>
  </Packages>
</Biml>

In 'package_01', the Linear mode adds a precedence constraint from SQL_CMD_1 to SQL_CMD_2. In 'package_02', the Parallel mode places the two tasks alongside each other with no constraint between them.

What This Buys​

Once the Biml file is in place, regenerating the packages is a single right-click. Changing a SQL command in the source updates every generated package. The same shape, scaled to many tables or many connections, is the foundation for the metadata-driven patterns that come next.