Building a Simple Package Generator with Biml

Why This Pattern Matters

A package generator turns rows in a metadata table into working SSIS packages. The metadata describes the packages: their names, their connections, parent-child relationships, and the constraint mode they should use. The Biml file describes the template that wraps each row. The compiler does the rest.

This walkthrough builds the smallest useful generator, with metadata for packages and connections only. The same pattern extends to data flows, mappings, and full extract-load patterns once the basics are in place.

The Metadata Schema

The minimum viable metadata for a generator needs three concepts:

• A package definition, naming the package and storing its top level properties
• A connection definition, storing the connection string and the connection type
• A link from packages to the connections they use

Four tables cover this:

Table	Purpose
pkg_definition	One row per package to generate
conn_definition	One row per connection
pkg_connection	Many to many link between packages and connections
conn_type	Lookup of connection type names (OLEDB, ADONET, FILE, and so on)

A self reference on 'pkg_definition' captures parent-child relationships, useful when one package executes another.

The 'pkg_definition' table holds the columns needed to drive package level Biml attributes:

Column	Type	Notes
PackageKey	Int	Identity, primary key
PackageName	Nvarchar(200)	Not null
ConstraintMode	Nvarchar(10)	Default Parallel
ProtectionLevel	Nvarchar(50)	Default EncryptSensitiveWithUserKey
ParentPackageKey	Int	Nullable, foreign key to PackageKey
GeneratePackage	Bit	Default 1

A new package definition is a single insert:

INSERT INTO meta.pkg_definition (PackageName)
VALUES ('LoadCustomerStage');

Splitting the Connection from the Generator

The Biml project starts with two files. The first holds only the connection back to the metadata database. Putting it in its own file makes it easy to edit when the metadata moves to a new server, without touching the generator.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <Connection
      Name="FrameworkConfig"
      ConnectionString="Data Source=DataServer;Initial Catalog=GeneratorMeta;Provider=SQLNCLI11.1;Integrated Security=SSPI;" />
  </Connections>
</Biml>

The Generator File

The generator file declares the language as C#, imports the data namespaces, and reads the package list from the metadata database. The Biml inside the loop emits one Package element per row, with attributes bound to the row values:

<#@ template language="C#" hostspecific="true" tier="0" #>
<#@ import namespace="System" #>
<#@ import namespace="System.Data" #>

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<#
  var configConn = (AstDbConnectionNode)RootNode.Connections["FrameworkConfig"];
#>
  <Packages>
<#
  var packagesToCreate = ExternalDataAccess.GetDataTable(
    configConn.ConnectionString,
    "SELECT PackageKey, PackageName, ConstraintMode, ProtectionLevel, GeneratePackage " +
    "FROM meta.pkg_definition p " +
    "WHERE GeneratePackage = 1 " +
    "ORDER BY p.PackageName");

  foreach (DataRow pkg in packagesToCreate.Rows) {
#>
    <Package
      Name="<#=pkg["PackageName"]#>"
      ConstraintMode="<#=pkg["ConstraintMode"]#>"
      ProtectionLevel="<#=pkg["ProtectionLevel"]#>">
      <Tasks>
        <Container Name="MainControlFlow" ConstraintMode="Linear">
          <Variables></Variables>
          <Tasks>
            <Expression Name="Placeholder" Expression="1" />
          </Tasks>
        </Container>
      </Tasks>
    </Package>
<# } #>
  </Packages>
</Biml>

The 'tier' attribute on the template directive tells the compiler the order in which Biml files run. Files with a lower tier run first, so this generator runs after the connection file (tier zero by default in this example, with the connection file being read on demand through 'RootNode.Connections').

Generating the Packages

In Visual Studio with BimlExpress, select both Biml files (shift-click), right click, and choose Check Biml For Errors. If the metadata query and the template both validate, right click again and choose Generate SSIS Packages. The compiler emits one .dtsx file per row in 'pkg_definition' where 'GeneratePackage' equals 1.

Each generated package contains a single sequence container with a placeholder Expression task. That placeholder is the spot where the data flow, the audit calls, and the framework specific tasks will go in subsequent iterations.

What This Sets Up

With the package list driven by metadata, adding a new package is an insert. The next pattern adds connections to each package: a 'pkg_connection' row points the package at the source and target connections, and the generator pulls those connection strings from 'conn_definition' inside the package element. After that, column-level mapping metadata feeds the data flow generation.

The result is a project where the entire ETL is described by a small set of relational rows, and the SSIS packages are a build artifact rather than something edited by hand.