Skip to main content

Why Biml: An Introduction

What Biml Is​

Biml is short for Business Intelligence Markup Language. It is an XML dialect that describes Microsoft Business Intelligence assets: SSIS packages, SSAS dimensions and cubes, and supporting database objects. A Biml compiler reads the markup and emits the matching .dtsx files, .ds tabular projects, or other artifacts. The generated files open and edit in Visual Studio like any package built by hand.

Biml element names track the SSIS object model: connections, then databases and schemas, then tables, then packages and tasks. A trivial example produces an empty package:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <Package Name="EmptyDemo" />
  </Packages>
</Biml>

The example produces a single .dtsx file with no tasks. Useless on its own, it shows how the language structure follows the SSIS object model.

BimlScript layers on top of the markup. Code nuggets like '<# ... #>' embed C# or VB.NET that runs at compile time. The script can read external metadata, loop over rows, evaluate expressions, and emit further Biml. That is what turns a static markup language into a generator.

Why Biml Exists​

Most enterprise SSIS portfolios contain repetitive packages. Source packages, the ones that pull data from a system of record into a staging table of similar shape, are the clearest example. Each package contains a truncate task and a data flow that copies the source table into the target. The metadata that varies between packages is small: the table name on the truncate, the source table on the OLE DB source, the destination table on the OLE DB destination.

A Source Package by Hand​

The source package shape expressed as Biml looks like this:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="Source" ConnectionString="provider=SQLOLEDB.1;data source=localhost;database=OperationsDW;integrated security=SSPI" />
    <OleDbConnection Name="Stage"  ConnectionString="provider=SQLOLEDB.1;data source=localhost;database=Stage;integrated security=SSPI" />
  </Connections>
  <Packages>
    <Package Name="Stage_Party" ConstraintMode="Linear">
      <Tasks>
        <ExecuteSQL Name="SQLT_Truncate" ConnectionName="Stage">
          <DirectInput>TRUNCATE TABLE Party</DirectInput>
        </ExecuteSQL>
        <Dataflow Name="DAFL_Load">
          <Transformations>
            <OleDbSource Name="OLE_Src" ConnectionName="Source">
              <ExternalTableInput Table="Party" />
            </OleDbSource>
            <OleDbDestination Name="OLE_Dest" ConnectionName="Stage">
              <ExternalTableOutput Table="Party" />
            </OleDbDestination>
          </Transformations>
        </Dataflow>
      </Tasks>
    </Package>
  </Packages>
</Biml>

For one package, the markup is more verbose than the equivalent SSIS designer steps. Projects with three or four similar packages do not justify the upfront investment. Projects with twenty or fifty source packages amortize it many times over.

Adding a Single Variable​

A useful intermediate step toward full automation is to introduce one variable for the table name and reference it everywhere. No loops, no metadata, just a single string assignment:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <# string tableName = "Party"; #>
  <Connections>
    <OleDbConnection Name="Source" ConnectionString="provider=SQLOLEDB.1;data source=localhost;database=OperationsDW;integrated security=SSPI" />
    <OleDbConnection Name="Stage"  ConnectionString="provider=SQLOLEDB.1;data source=localhost;database=Stage;integrated security=SSPI" />
  </Connections>
  <Packages>
    <Package Name="Stage_<#=tableName#>" ConstraintMode="Linear">
      <Tasks>
        <ExecuteSQL Name="SQLT_Truncate" ConnectionName="Stage">
          <DirectInput>TRUNCATE TABLE <#=tableName#></DirectInput>
        </ExecuteSQL>
        <Dataflow Name="DAFL_Load">
          <Transformations>
            <OleDbSource Name="OLE_Src" ConnectionName="Source">
              <ExternalTableInput Table="<#=tableName#>" />
            </OleDbSource>
            <OleDbDestination Name="OLE_Dest" ConnectionName="Stage">
              <ExternalTableOutput Table="<#=tableName#>" />
            </OleDbDestination>
          </Transformations>
        </Dataflow>
      </Tasks>
    </Package>
  </Packages>
</Biml>

Producing another package is now a matter of changing 'tableName' and regenerating. It is not the most automated solution possible, but it removes the per-package XML editing without requiring any C# beyond a single string assignment.

Driving the Same Template From Metadata​

The next step replaces the single variable with a loop over metadata. The fragment below issues a CREATE statement for every table the Biml model knows about, regardless of whether there is one table or a thousand:

<# foreach (var tbl in RootNode.Tables) { #>
  <ExecuteSQL Name="Create <#= tbl.Name #>" ConnectionName="StagingTgt">
    <DirectInput><#= tbl.GetDropAndCreateDdl() #></DirectInput>
  </ExecuteSQL>
<# } #>

The 'GetDropAndCreateDdl' method ships with Biml, so the SQL itself is not hand assembled. The same pattern wraps the staging package shown earlier: one Biml file, one foreach over a metadata table, dozens of generated packages. Productivity gains compound from there. Once a pattern is in BimlScript, scaling it is a matter of adding rows to the metadata, not files to the project.

The structured nature of the source also makes source control useful as more than a backup target. Diffs are meaningful, code review is possible, and refactoring happens in one place.

How Compilation Works​

The Biml engine processes a project in two stages.

  1. The BimlScript runs and produces pure Biml XML.
  2. The XML is interpreted and translated into Microsoft BI artifacts: .dtsx files, .sql files, or whatever else the project targets.

The transition is transparent in BimlExpress. BimlStudio exposes the intermediate Biml so that the XML can be inspected before the final translation runs.

The Tools​

Three frontends author and run Biml.

  • BimlExpress is a free Visual Studio add-in that compiles Biml inside the SSIS project types. It provides syntax highlighting, IntelliSense, and the Generate SSIS Packages command. It does not include build automation, SSAS support, or the import of existing .dtsx files.
  • BimlOnline is a free, browser based editor. It visualizes metadata and can convert existing .dtsx files into Biml. It does not include build automation or SSAS support.
  • BimlStudio is the commercial IDE. It includes build automation, code preview, the full Biml feature surface, metadata visualization, .dtsx import, easier debugging, and the BimlBundles and BimlTransformers tooling. BimlStudio is available as a perpetual license or a monthly subscription.

A pragmatic ramp up: start with a BimlStudio trial because the metadata visualizer and code preview accelerate learning. After the trial, drop down to BimlExpress for ongoing free use. Move back to BimlStudio when the advanced features justify the license.

Production systems can be built entirely on the free tier. The paid products extend the free tier rather than gating fundamental capability.

The Wider Ecosystem​

Three additional products extend Biml beyond the core compiler. All three are part of BimlStudio.

  • BimlFlex is a data warehouse automation framework built on Biml. It ships predefined patterns for common loads (staging, persistent staging, dimensional models, Data Vault) and Extension Points where teams override the standard pattern for one column, one table, or one entire flow.
  • BimlBundles let a vendor or consultancy package and ship a Biml framework as an encrypted artifact (.bimlb). Customers can use the bundle and extend it through Extension Points, but the source Biml stays protected.
  • BimlTransformers store changes against existing Biml rather than modifying the original. The pattern is similar to .NET configuration transforms: a base Biml file remains untouched, and a Transformer (.bimlt) file declares the differences for a particular environment, customer, or scenario. Useful when migrating an existing solution to a new platform or when one base solution must be customized per customer without forking the source.

Beyond SSIS​

Biml is not limited to data integration.

  • SSAS. Both tabular and multidimensional models can be authored in Biml, though this surface is BimlStudio only.
  • Cube processing automation. A loop over an OLAP database can read the dimension and cube list and generate the matching processing tasks for SSIS.
  • Azure Data Factory. The DataFactory, LinkedServices, Datasets, Pipelines, and Triggers tags generate Azure Data Factory v2 JSON from source metadata.
  • T-SQL utilities. The Biml object model also fits classic T-SQL tasks: sample data generation, view and index health checks, and DACPAC alternatives. None of these require Biml, but the object model often makes them simpler than dynamic SQL.

Maturity and Safety Net​

Biml has been generally available for years and is in production at organizations of every size. Even in the worst case where a Biml project is abandoned, the SSIS packages it has generated remain valid .dtsx files and can be edited or executed without Biml. Adoption does not lock the project into a proprietary runtime.

Where to Go Next​