Skip to main content

Beginning with Biml

Why This Pattern Matters​

Biml replaces large amounts of clicking and dragging in the SSIS designer with a single XML document that the compiler turns into one or more SSIS packages. Once a project standardizes on Biml, every package follows the same pattern, and any change to the pattern only has to be made in one place.

This walkthrough explains what Biml describes, how to install it, and a short learning path that builds toward fully automated package generation.

What Biml Describes​

Biml is a domain specific XML language for business intelligence objects. It can describe:

  • SSIS packages and their tasks
  • Databases, schemas, tables, and columns
  • SSAS cubes, facts, and dimensions (in BimlStudio)

BimlScript extends Biml with C# or VB.NET code nuggets. Code nuggets allow patterns such as importing table schemas from a live database, replacing literal values with computed expressions, including text from another file, and looping over metadata to emit one package per source object. Plain Biml is useful on its own; BimlScript is what turns repetitive design work into reusable templates.

Installing Biml​

Two paths exist:

  • BimlExpress is a free Visual Studio extension for SSDT BI. It includes everything needed to write and compile Biml for SSIS and is the right starting point.
  • BimlStudio is a paid IDE that adds reverse engineering of existing SSIS packages, SSAS support, and a graphical designer that emits Biml automatically.

Either tool can compile the same Biml. Starting with BimlExpress avoids any cost while learning.

A Five Step Learning Path​

Working through these five exercises in order builds a foundation that almost every BimlScript template draws on:

  1. Create a blank package in Biml and generate it. The smallest valid Biml file is six lines and produces an empty SSIS package.
  2. Add a project level connection in Biml and generate it. Confirm that 'CreateInProject="true"' promotes the connection to a shared project connection manager.
  3. Build a staging package: an Execute SQL Task that truncates a target table, followed by a data flow that selects from a source table and inserts into the truncated target.
  4. Reverse engineer an existing dimension package and an existing fact package using BimlStudio. Read the resulting Biml, then recreate the same packages by hand.
  5. Build a small Biml library that captures the team's standard patterns: import from flat file, import from source table, populate a fact table, populate a Type 1 dimension, populate a Type 2 dimension. Each pattern lives in its own template file.

After step 5 the next jump is into BimlScript proper. A reasonable first BimlScript exercise is a replicator script that reads the table list from one database and emits one staging package per table into a destination database.

Why Patterns Pay Off Early​

The benefit of Biml shows up the first time a project needs many packages that all follow the same shape. A team can hand a developer a Biml template, a list of source tables, and a one paragraph instruction sheet that says which lines to edit. Generated packages all look the same, all follow the same conventions, and never suffer from the corrupted metadata or missed setting that often comes from copying packages by hand.

The same argument scales up. A senior developer can capture the team's preferred staging, dimension, and fact patterns once. A junior developer can then generate fully wired packages by editing a metadata table or a small driver Biml file rather than building each package in the designer.