Introduction to Biml

What is Biml?

Business Intelligence Markup Language (Biml, not BiML or BIML) is a domain-specific XML used for describing the design of SQL Server Integration Services (SSIS) packages. When these files are compiled by a program like BIDS Helper, they produce real, usable SSIS packages.

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

Snippet 1. The base template Biml file generated by BIDS Helper.

From this base, you can add database connections:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <Connection Name="Source" ConnectionString="Provider=SQLNCLI10.1;Data Source=localhost;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=bimlDemo" />
  </Connections>
</Biml>

Snippet 2. The connections collection with one database connection.

A package with a couple Execute SQL Tasks:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <Connection Name="DBConn" ConnectionString="Provider=SQLNCLI10.1;Data Source=localhost;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=bimlDemo" />
  </Connections>
  <Packages>
    <Package Name="DemoPackage" ConstraintMode="Parallel">
      <Tasks>
        <ExecuteSQL Name="Truncate Loans Table" ConnectionName="DBConn">
          <DirectInput>TRUNCATE TABLE LOANS;</DirectInput>
        </ExecuteSQL>
        <ExecuteSQL Name="Truncate Borrowers Table" ConnectionName="DBConn">
          <DirectInput>TRUNCATE TABLE BORROWERS;</DirectInput>
        </ExecuteSQL>
      </Tasks>
    </Package>
  </Packages>
</Biml>

Snippet 3. A fully realized Biml file that generates one package.

And then use the Biml file to generate an SSIS package:

Figure 1. The generated DemoPackage.dtsx

What is Bimlscript?

So far Biml just appears to be another way to write out SSIS packages (not to mention one that seems to take longer than just building it in SSIS), and that’s probably true. If you’re only working on one package with a simple data flow. Biml really starts to prove itself when you include coding components (C# or VB.NET), creating something known as Bimlscript.

The package created above is really good at truncating the LOANS and BORROWERS tables, but what if new tables were added to the database that will need to be truncated as part of this process? In pure Biml, we could simply add a new Execute SQL Task for the new table:

<Package Name="DemoPackage" ConstraintMode="Parallel">
  <Tasks>
    <ExecuteSQL Name="Truncate Loans Table" ConnectionName="DBConn">
      <DirectInput>TRUNCATE TABLE LOANS;</DirectInput>
    </ExecuteSQL>
    <ExecuteSQL Name="Truncate Borrowers Table" ConnectionName="DBConn">
      <DirectInput>TRUNCATE TABLE BORROWERS;</DirectInput>
    </ExecuteSQL>
    <ExecuteSQL Name="Truncate References Table" ConnectionName="DBConn">
      <DirectInput>TRUNCATE TABLE REFERENCES;</DirectInput>
    </ExecuteSQL>
  </Tasks>
</Package>

Snippet 4. Adding a new task can be a simple copy paste job in Biml.

That’s certainly faster than editing the package by hand, not to mention a Biml file is easier to keep track of in source control than an SSIS package. But it’s not very dynamic. What if we could drive the process from a metadata table?

bimlDemo.dbo.metadata

By adding some C# to our Biml file, we can initiate a database connection and use this data to drive our package creation.

<!--
<#@ import namespace="System.Data.SqlClient" #>
<#
  SqlConnection metadataConnection = new SqlConnection("Data Source=localhost;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=bimlDemo");
  metadataConnection.Open();
  SqlCommand metadataQuery = new SqlCommand("SELECT table_name, truncate_nightly FROM metadata", metadataConnection);
  SqlDataReader metadataReader = metadataQuery.ExecuteReader();
#>
-->
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <Connection Name="DBConn" ConnectionString="Provider=SQLNCLI10.1;Data Source=localhost;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=bimlDemo" />
  </Connections>
  <Packages>
    <Package Name="DemoPackage" ConstraintMode="Parallel">
      <Tasks>
        <!--<# while(metadataReader.Read()){ #>-->
          <!--<# if(metadataReader["truncate_nightly"].Equals("Y")) { #>-->
            <ExecuteSQL Name="Truncate <#=metadataReader["table_name"] #>" ConnectionName="DBConn">
              <DirectInput>TRUNCATE TABLE <#=metadataReader["table_name"] #>;</DirectInput>
            </ExecuteSQL>
          <!--<# } #>-->
        <!--<# } #>-->
      </Tasks>
    </Package>
  </Packages>
</Biml>

Snippet 5. A simple Bimlscript file using external metadata.

The above code connects to a database to read the metadata table created earlier. It then loops through the results creating an Execute SQL Task for each row where truncate_nightly equals Y. When the package is opened, we see that this is the case.

Figure 2. The new, dynamically generated DemoPackage.dtsx

Now, if we want to change whether or not a table is truncated by the package (or add a new table to the package) we simply have to update the metadata table and regenerate the package. Even for this simple change, that could represent upwards of an hour of work to do manually. It is now done in minutes.

Why Biml?

So, what’s the point? There are many reasons to start using Biml, but here are a few of the recurring points.

Time Saving

Probably the single most discussed selling point of Biml is the time savings. A typical example focuses on a pretty common pattern, moving data from tables in one database to a remote database. They’ll assign a development time of around 1-4 hours (and that’s not unreasonable) to manually make a package to do move this data. Setting up a Biml file to do this for one table would probably take around the same amount of time. But as we saw above, once you bring in Bimlscript the creation of tasks and packages (you can put your looping constructs around whichever part of the Biml file) scales remarkably well. So, if you were making a similarly patterned package for dozens or even hundreds of tables, the manual method time requirement would grow nearly linearly while the Bimlscript time investment would drop off quickly.

Another time savings angle comes from the ease of applying changes to existing Biml projects. Say you have a project that is composed of one hundred simple data flows (just a source and a destination). To these data flows we want to add increased logging of errors, so for each flow we will add a row count from the destination error output and then run that row count to a new destination. In Biml this would be almost trivial, but it’s not hard to imagine how time consuming this would be if done by hand. Another simple example would be changing all of the destination objects to use identity insert. By hand it would involve opening up every destination object and checking a box, but in Biml it would be as simple as adding an attribute to the destination tag inside the loop.

Code Reuse

Using tiering and includes, Biml files can be combined allowing a developer to split up chunks of code into reusable pieces. Common variables or connections can be stored in their own Biml files and useful snippets of Biml code (like logging setup) can be stored in their own files to be included later:

<LogEvents>
  <LogEvent EventName="OnError" />
  <LogEvent EventName="OnPostExecute" />
  <LogEvent EventName="OnPreExecute" />
  <LogEvent EventName="OnWarning" />
</LogEvents>
<LogProviders>
  <TextLogProvider Name="LogFileConnection" ConnectionName="LogFileConnection" />
</LogProviders>

Snippet 6. Contents of Logging.biml.txt

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <Connection Name="DBConn" ConnectionString="Provider=SQLNCLI10.1;Data Source=localhost;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=bimlDemo" />
    <FileConnection Name="LogFileConnection" FilePath=".\logfile.txt" FileUsageType="CreateFile" />
  </Connections>
  <Packages>
    <Package Name="DemoPackage" ConstraintMode="Parallel">
      <Tasks>
        <ExecuteSQL Name="Truncate Loans Table" ConnectionName="DBConn">
          <DirectInput>TRUNCATE TABLE LOANS;</DirectInput>
        </ExecuteSQL>
        <ExecuteSQL Name="Truncate Borrowers Table" ConnectionName="DBConn">
          <DirectInput>TRUNCATE TABLE BORROWERS;</DirectInput>
        </ExecuteSQL>
      </Tasks>
      <!--<#@ include file=".\Logging.biml.txt" #>-->
    </Package>
  </Packages>
</Biml>

Snippet 7. The package from Snippet 3 now much improved with package level logging.

Standards & Conventions

Biml also makes it easier to follow standards and conventions within an organization. Naming conventions can be scripted into the package creation, making consistent naming part of the process. As mentioned in the code reuse section, common variables can be kept in a separate Biml file granting them to any package made including that Biml file. This can be useful in situations where an external SSIS framework requires common variables or parameters for execution. Things like logging can be standardized like in the previous example.

Source Control

SSIS Packages are software objects and subject to versioning. To date this has been a challenge, however, since the dtsx XML format is overcrowded with build specific IDs and GUIDs. Biml strips much of that away leaving just the relevant logic making comparing two versions of the same file more feasible.