Don't Repeat Your Biml - Tiered Biml Files

Many Biml solutions start off very simple, with just a single Biml file that generates a few SSIS packages. Most developers quickly see the need for a more complex solution for multiple sources. One way to reuse code and apply the Don’t Repeat Yourself software engineering principle in Biml is to use Tiered Biml Files.

In addition to using Tiered Biml Files, there are four other main ways you can avoid repeating your Biml code:

• Include Files
• CallBimlScript
• C#/VB Code Files
• Transformers (BimlStudio feature only)

In this post, we will look at how to use Tiered Biml Files.

Tiered Biml Files

First of all, what are tiers? You can think of tiers as layers built on top of each other. The lowest layer is tier 0, the layer above is tier 1, then 2, 3, 4 and so on:

Another way to think of tiers is to think of them as steps. The first step is tier 0, the next step is tier 1, then 2, 3, 4 and so on:

From Manual Steps in SSIS to Tiers in Biml

When creating SSIS packages, we often need to do things in a specific order. We need to create a connection to our source before we can load data from that source. We also need to create the loading packages before we can create an orchestrator package that references and executes those loading packages.

If we use Biml to create our SSIS packages, we still need to do things in a specific order. Instead of doing each of these steps manually, we split our Biml code into multiple files and specify a tier per file. The tiers then determine the order in which we want to compile our Biml code and create our objects.

Instead of doing the manual steps in SSIS like described above, we can create our connections in a tier 0 file, our loading packages in a tier 1 file, and an orchestrator package in a tier 2 file:

When we select all three Biml files and click Generate SSIS Packages, the Biml compiler will create all objects step-by-step in-memory, and then finally generate all our packages. The SSIS package generation looks like one single process to us but is actually executed as multiple steps behind the scenes.

Defaults

If you don’t explicitly specify tiers per file, the Biml compiler will treat all Biml files without BimlScript as tier 0 and all Biml files with BimlScript as tier 1. You have no control over the order of how objects will be created within each tier, so you cannot reference objects created in the same tier. This is simply because they have not been created yet at the time you are trying to reference them.

Please note: While I use 0, 1, 2 and so on in these examples, you can use any number series, for example 0, 10, 20 or 101, 102, 200, 300 and so on. Tier numbers must be positive integers, but they do not have to be consecutive. By using a number series like 0, 10, 20 or 101, 102, 200, 300, you also leave gaps that allow for new tiers to be used at a later stage in development. Use a number series that makes sense to you 💡

The Biml RootNode

Before we look at some example code for tiered Biml filed, it is important to understand what the Biml RootNode is. Simply put, the RootNode is the root element, or parent, of all objects in your Biml project. You can reference anything in your project by starting with the RootNode.

Biml code is compiled tier by tier. For each tier, all defined objects are created and added to the RootNode. This allows us to go through the RootNode in higher tiers to reference objects created in lower tiers. If we create our Connections in a Tier 0 file, we can use those connections in any file that has a Tier of 1 or higher.

(A more accurate and technical description of the Biml RootNode can be found in the official Biml Language documentation and in the official Biml API documentation. You can see all RootNode properties and methods in the Biml AstRootNode documentation.)

Below are a few common and useful snippets for querying the RootNode. Please note that if you use the Preview feature in BimlExpress, the snippets will show objects from all currently open Biml files, so make sure you open your other files first.

Print the Biml for all defined objects in your project:

<#=RootNode.GetBiml()#>

Loop through all defined connections and print the name of each connection:

<# foreach (var connection in RootNode.Connections) { #>
  <#=connection.Name#>
<# } #>

Loop through all defined OLE DB connections and print the connection string for each connection:

<# foreach (var connection in RootNode.OleDbConnections) { #>
  <#=connection.ConnectionString#>
<# } #>

Print the connection string for the OLE DB connection named Source:

<#=RootNode.OleDbConnections["Source"].ConnectionString#>

Example: Three Tiered Biml Files

Using the example from the beginning of this post, we can create our connections in a tier 0 file, our loading packages in a tier 1 file, and an orchestrator package in a tier 2 file:

0-Connections.biml:

<#@ template tier="0" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="Source" ConnectionString="..." />
    <OleDbConnection Name="Destination" ConnectionString="..." />
  </Connections>
</Biml>

The Tier 0 Biml file defines the source and destination connections. Since this is a Biml file without BimlScript, it is implicitly Tier 0, and we technically do not need to specify the tier. However, to make it easier to read and understand Biml code, I always explicitly specify the tiers in each of my tiered Biml files.

1-LoadingPackages.biml:

<#@ template tier="1" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <# foreach (var table in RootNode.OleDbConnections["Source"].GetDatabaseSchema().TableNodes) { #>
      <Package Name="Load_<#=table.Schema.Name#>_<#=table.Name#>" ConstraintMode="Linear">
        ...
      </Package>
    <# } #>
  </Packages>
</Biml>

The Tier 1 Biml file defines the loading packages. First, we call RootNode.OleDbConnections[“Source”] to get the OLE DB connection named “Source” that we defined in the Tier 0 file. Then we call GetDatabaseSchema() to import the metadata from this connection. Finally, we call TableNodes so we can loop over the table metadata and create one package per source table.

2-OrchestratorPackage.biml:

<#@ template tier="2" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <Package Name="OrchestratorPackage">
        <Tasks>
          <# foreach (var package in RootNode.Packages) { #>
            <ExecutePackage Name="Execute <#=package.Name#>">
              <ExternalProjectPackage Package="<#=package.PackageFileName#>" />
            </ExecutePackage>
          <# } #>
        </Tasks>
    </Package>
  </Packages>
</Biml>

The Tier 2 Biml file defines an orchestrator package that executes all the loading packages. We call RootNode.Packages to get all the packages that were defined from our source metadata in the Tier 1 file, and then we create an Execute Package task for each of the loading packages.

To generate all the SSIS Packages, we select all three files, right-click, and click Generate SSIS Packages.

We can now create multiple Tier 0 files, one for each of our sources. Whenever we need to (re-)generate our SSIS packages, we just select the appropriate Tier 0 file as well as the Tier 1 and Tier 2 files. This way, we can reuse two of our three files!

Summary

Use tiered Biml files to solve logical dependencies and build solutions in multiple steps behind the scenes. Specify the tier per file by using the template directive: <#@ template tier=“0” #>. Biml files without BimlScript are implicitly Tier 0, and Biml files with BimlScript are implicitly Tier 1. For each tier, objects are added to the RootNode so they can be referenced by higher tier files.

About the Author

Cathrine Wilhelmsen is a Microsoft Data Platform MVP, international speaker, author, blogger, organizer, and chronic volunteer. She loves data and coding, as well as teaching and sharing knowledge - oh, and sci-fi, gaming, coffee and chocolate 🤓