Skip to main content

Tiered Biml Files

Why This Pattern Matters​

Most Biml solutions start as a single file that generates a handful of packages. The first time a project needs to reuse the same connection definition across many files, or build an orchestrator that references packages defined elsewhere, that single file pattern stops scaling. Tiered Biml files split the work into ordered stages, where each tier can read everything that earlier tiers produced. This is one of several techniques for keeping Biml DRY, alongside include files, 'CallBimlScript', C# code files, and BimlStudio transformers.

What a Tier Is​

A tier is a layer in the build. Tier 0 runs first, tier 1 runs next, then tier 2, and so on. The tier number does not have to be sequential. Any positive integer is valid, so a project can use 0, 10, 20, 30 to leave room for future tiers without renumbering existing files.

When the compiler runs, it processes all files at the lowest tier together, then moves up to the next tier. Within a tier there is no guaranteed order, so files at the same tier cannot reference objects defined in other files at that same tier. They can only reference objects from lower tiers, which by then have already been added to the 'RootNode'.

Default Tiers​

If a file does not declare a tier, the compiler infers one. Flat Biml files with no BimlScript code nuggets are treated as tier 0. Files that include BimlScript are treated as tier 1. The tier is set explicitly with the template directive at the top of the file:

<#@ template tier="0" #>

The RootNode​

The 'RootNode' is the parent of every object in the Biml project. After tier 0 finishes, every connection, database, schema, and table defined at tier 0 is reachable from the 'RootNode'. After tier 1 finishes, the packages defined at tier 1 are also reachable. Higher tier files use the 'RootNode' to walk over those objects.

A few common reads off the 'RootNode':

Print the Biml for every object in the project:

<#=RootNode.GetBiml()#>

Loop over every connection by name:

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

Loop over only the OLE DB connections and print their connection strings:

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

Look up a single connection by name:

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

Example: Three Tiered Files​

A common split puts connections in tier 0, loading packages in tier 1, and an orchestrator package in tier 2.

The tier 0 file declares the source and destination connections:

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

The tier 1 file looks up the source connection from the 'RootNode', imports its schema, and creates one package per source table:

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

The tier 2 file walks over every package the tier 1 file produced and wraps it in an Execute Package task inside an orchestrator:

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

To build the project, select all three files together, right click, and choose Generate SSIS Packages. The compiler will process tier 0 first, then tier 1, then tier 2, and emit the final packages in one operation.

Reusing Tier Files Across Sources​

Once the split is in place, the tier 0 file is the only piece that has to change to point at a new source. Adding a second source means adding a second tier 0 file with a different set of connections. The same tier 1 and tier 2 files can be reused to generate the loading packages and the orchestrator for that new source, because they read the connection list from the 'RootNode' rather than from a hardcoded reference.

Summary​

Tiered Biml files solve dependency problems by giving each stage its own file and a tier number that orders the build. Lower tier files create objects, the compiler adds them to the 'RootNode', and higher tier files read those objects. The result is a project where connections, table definitions, packages, and orchestrators can each live in the file that owns them and still reference each other safely.