Skip to main content

Meta Data Driven SSIS Solution with Biml

What This Pattern Produces​

The objective is a small Kimball style data warehouse loaded by SSIS packages that are generated from metadata rather than hand built. Biml is the markup that describes each package; BimlScript is the embedded code that turns rows in a metadata table into many similar packages without copy and paste.

The components in the example are:

  • A source database modeled on a typical operational schema, for example a sales order database such as the Northwind sample.
  • A warehouse database, for example 'Mart', that holds the dimensional model.
  • A staging database, for example 'Mart_Stg', that buffers raw extracts before they are merged into the warehouse.
  • A metadata database, for example 'Mart_Meta', that holds a source to target mapping table. Each row describes one source table, the target staging table, and the columns to project. The contents of this table drive package generation.

How the Pieces Fit Together​

The metadata table is the single source of truth. Two BimlScript files consume it:

  • A 'TablePackage' template that produces one SSIS package per row in the mapping table. Each generated package extracts one source table into its staging counterpart.
  • A 'MasterPackage' template that produces one orchestration package which executes every table package in the correct order.

Because the metadata table drives both files, adding a new source table is done by inserting a row in the mapping table and regenerating. There is no need to edit the SSIS project, copy a template package, or rewire connections.

The Build Sequence​

The series the pattern follows breaks down into five phases:

  1. Architecture overview, which is the content of this walkthrough.
  2. Create the source, staging, warehouse, and metadata databases, then populate the source to target mapping table.
  3. Build a single 'TablePackage' Biml file that produces a working extract package for one mapping row. This is the vanilla template.
  4. Wrap the vanilla template in BimlScript that loops over every mapping row and emits one package per row.
  5. Build the 'MasterPackage' Biml file that generates the orchestration package which executes every emitted child package.

The BimlExpress add in for Visual Studio is enough to compile and run the resulting Biml, so no paid tooling is required for this pattern.

Why Drive Generation From Metadata​

A metadata driven pipeline shifts the work from building packages to maintaining metadata. The same mapping table can also drive documentation, lineage reporting, and validation queries, which means a single change in metadata propagates to every artifact that consumed it. The trade off is the upfront design effort to settle on a metadata model that is expressive enough to describe the variations the pipeline needs and small enough to keep maintainable.