Skip to content

Don’t Repeat Your Biml – Tiered Biml Files

Biml (Business Intelligence Markup Language) - 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:

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:

Biml Tiers Visualized as Layers

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:

Biml Tiers Visualized as Steps

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 a master 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 a master package in a tier 2 file:

Biml Tiers with Connections Packages and Master Package

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 a master package in a tier 2 file:

Biml Tiers with Connections Packages and Master Package

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-MasterPackage.biml:

<#@ template tier="2" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <Package Name="MasterPackage">
        <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 a master 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:

Biml Tiers: 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, BimlHero Certified Expert, Microsoft Certified Solutions Expert, international speaker, author, blogger, and chronic volunteer who loves teaching and sharing knowledge. She works as a Senior Business Intelligence Consultant at Inmeta, focusing on Azure Data and the Microsoft Data Platform. She loves sci-fi, chocolate, coffee, craft beers, ciders, cat gifs and smilies :)

Comments

Hi! This is Cathrine. Thank you so much for visiting my blog. I'd love to hear your thoughts, but please keep in mind that I'm not technical support for any products mentioned in this post :) Off-topic questions, comments and discussions may be moderated. Be kind to each other. Thanks!

How I can schedule(SQL Job) BIML SCRIPTING to generate DTS packages auto-generated for structure and Data load??

Hi Harpreet, to automate the generation of SSIS packages, you need a BimlStudio license. Once you have a license, you can generate packages with a command-line tool so you can include it in a job or script.

How much it cost us?

You will find all details on the Varigence website.

Hi Cathrine

How do I get the result of a target tier 00 file into the target tier 10 file?
I’ve create to files like you did, but if i save file “10” it did not use the result of the “00”.
How do I make the results of “00” permanent?
Do I have to create a package first?
But it looks like you did it on the fly.
Did I just miss the clue?

Best regards
Hinrich

Hi Hinrich, I’m not sure I understand your question. Higher tier files do not save any information from lower tiered files. That would defeat the entire purpose of using tiered files, as you would not be able to reuse the higher tier files. When you generate your SSIS packages, you need to select all your tiered files, and they will all be interpreted and used as if you had created one single, hardcoded file. While you are developing your solution, you need to have all the tiered files open in your editor to be able to see the results from a lower tier file in the preview pane of a higher tier file. The workflow would be to open your files in order from lowest to highest tier.

Hi! This is Cathrine (again). Just a reminder. I'd love to hear your thoughts, but please keep in mind that I'm not technical support for any products mentioned in this post :) Off-topic questions, comments and discussions may be moderated. Be kind to each other. Thanks!

Leave a Reply to Hinrich Schencking Cancel reply