Biml

Now that we’ve kicked around the Biml tires for a bit and understand the basic constructs within the language, you may also have come to the following conclusions:

Especially if we’re developing packages as part of en ETL process, the initial staging packages will all follow a similar pattern. While we could just copy and paste the relevant code snippets for all of these packages, there must be a better way to generate the Biml code.
Using one large Biml file to generate all our packages will be messy and difficult to maintain.
Many parts of the code will be repeated for each package (think about connections), and if there’s a way to reuse the same code it will be much more elegant (and useful) as a solution.

Luckily for us, all of the above is possible with Biml and/or BimlScript. Instead of rehashing all of the details here, I’ll briefly explain some of the concepts and point to other peoples’ posts (who have done a much better job than I can).

Include Files

Biml allows you to include the entire contents of another Biml file and reuse the code. Think about the scenario where you have the same connections in each SSIS package, and instead of copying the same snippet over and over again it would be easier to just reuse it.

To use our examples from this series, it would be possible to create a “Connections.biml” file that looks like this:

<OleDbConnection Name="BI_Admin" CreateInProject="true" ConnectionString="Data Source=<>;Initial Catalog=BI_Admin;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" /> 
<OleDbConnection Name="Staging" CreateInProject="true" ConnectionString="Data Source=<>;Initial Catalog=Staging;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" /> 
<OdbcConnection Name="RLM" CreateInProject="true" ConnectionString="<>" DelayValidation="true" />

Note that we don’t have to include the <Biml> and other tags to make it a fully functional Biml script. We only want to inject the contents of this file into another, so we only need the actual tags we’re going to inject and the result will look something like this:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
 
    <Connections>
        <#@ include file= "Connections.biml" #>
    </Connections>
 
    <Packages>
        ... 
    </Packages>
 
</Biml>

For a more comprehensive explanation, look at the following blog from Catherine Wilhelmsen: Don’t Repeat Your Biml – Include Files.

Tiered Execution

It may be necessary to enforce the order of execution in your Biml files, especially when you have project-level connections, master packages and child packages all in separate Biml files. In my opinion, it’s an overall good practice to assign a tier to all of your Biml files:

<#@ template language="C#" tier="1" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
 
    <Connections>
        <#@ include file= "Connections.biml" #>
    </Connections>
 
    <Packages>
        ... 
    </Packages>
 
</Biml>

Before we go any further, you may have noticed that we slipped a few new elements in here…so let’s explain them first.

The end goal is to automate the creation of SSIS packages, and in order to do that we are going to mix Biml code (which is XML that will generate the SSIS components) and procedural code (C# or VB.NET that we’ll use to do other fun stuff). The Biml compiler needs to know how the code should be interpreted, and that’s why we have to use the <# escape characters to indicate any non-Biml code nuggets.

Scott Currie explains the different types of code nuggets in more detail here, including the template directive we’ve used to indicate the language and tier of the Biml script file.

Biml Automation

The final piece to the puzzle is automation, and with everything we’ve uncovered thus far it is only a matter of putting the pieces together so that we can fully automate SSIS package generation. As with many other things, the most difficult part is getting started…but we’ve done that here and in the next few blog posts we will focus on the different ways in which we can automate everything.

Happy Biml-ing!!