Last time I wrote about how BIML lets you generate a stack of near-identical SSIS packages from a template instead of building them by hand. That post ended with a small confession: my example looped over a hardcoded list of table names. Useful for teaching, but if you have to edit an array every time your source changes, you've just moved the manual work up a level rather than removing it.

The real payoff is metadata-driven generation — where the list of what to build comes from a source of truth that already exists, so adding a table to your platform means changing data, not code. This post is about the patterns that get you there.

Where the metadata should come from

You have two honest options, and they suit different situations.

Read it live from the database schema. BimlScript can interrogate a connection and hand you back the tables and columns that actually exist right now. This is brilliant for a straight "stage everything in this source" job, because the generator can never drift out of sync with reality — if a column appears in the source, the next generation picks it up.

<#
    var db = RootNode.OleDbConnections["Source"]
             .GetDatabaseSchema(null, null, ImportOptions.None);
    var tables = db.TableNodes;
#>

Drive it from a control table you own. Sometimes you don't want "everything" — you want these tables, loaded this way, in this order, some full and some incremental. For that, a small metadata table you maintain in SQL is far better, because it captures decisions the schema can't:

CREATE TABLE etl.SourceObject (
    SchemaName   sysname       NOT NULL,
    TableName    sysname       NOT NULL,
    LoadType     varchar(20)   NOT NULL,  -- 'full' | 'incremental'
    KeyColumn    sysname       NULL,
    IsEnabled    bit           NOT NULL DEFAULT 1
);

BimlScript reads that table at generation time and builds accordingly. My rule of thumb: use the live schema when you genuinely want a faithful mirror of the source; use a control table the moment human decisions ("load this one incrementally, skip that one") enter the picture. In practice a real platform ends up using both.

Pattern 1: separate the metadata tier from the emission tier

The single most important structural habit is to split gathering the metadata from emitting the packages, and to make them run in the right order using tiers.

A tier is just BimlScript's way of saying "run this file before that one." You put your metadata-gathering logic in a lower tier so it runs first and populates a shared collection; the package-emitting file runs in a higher tier and reads what the first one prepared.

<#@ template tier="1" #>
<#
    // Tier 1: gather once, expose to later tiers
    var objects = /* query etl.SourceObject here */ ;
#>

Get this ordering wrong and you'll hit the classic beginner failure: the emission loop runs before the metadata exists, finds an empty list, and cheerfully generates nothing at all — no error, just an empty output folder and a confused you. When BIML produces nothing, "check your tiers" is the first thing to suspect.

Pattern 2: one included template per load type

Don't write one monstrous script with a giant if load_type == "incremental" branch in the middle of your XML. It becomes unreadable fast. Instead, keep a small, focused BIML file per load pattern — one for full loads, one for incremental — and have your loop call the right one for each table.

<# foreach (var obj in objects) { #>
    <#= CallBimlScript(
          obj.LoadType == "incremental" ? "IncrementalLoad.biml" : "FullLoad.biml",
          obj.SchemaName, obj.TableName, obj.KeyColumn) #>
<# } #>

Now each pattern lives in one place. Improve the incremental template once, regenerate, and every incremental package inherits the improvement. That's the whole promise of BIML honoured properly: a fix happens in exactly one location and propagates everywhere, instead of being copy-pasted into fifty packages and forgotten in the fifty-first.

Pattern 3: generate the plumbing too, not just the packages

Once you're thinking this way, you realise the individual load packages aren't the only repetitive thing. So is the orchestration — the master package that runs them all in the right order — and so are the project parameters for your connections. Generate those as well. Let the same metadata that produced the loads produce the master package that sequences them. The less you assemble by hand, the less there is to quietly get wrong.

The goal isn't "I used BIML." It's "there is exactly one place where each decision lives, and the packages are just its output."

Debugging BimlScript when it fights you

BimlScript will fight you early on, and the fights are nearly always the same three, so here's the cheat sheet:

  • Preview before you generate. Expand and inspect the generated BIML before you let it emit packages. Catching a bad loop in the preview saves you from generating two hundred broken packages and deleting them again.
  • Empty output? Suspect tiers first. If generation produces nothing at all, nine times out of ten your emission ran before your metadata did. Check the tier ordering before you check anything else.
  • A nugget printed literally? If you see <#= table #> sitting in the output as plain text, you've used a control nugget where you needed an expression one, or the reverse. It's always that.

None of these are in the documentation in a way that helps at 9pm. They're the bruises, written down so they can be yours cheaply.

The mindset, restated

I said last time that the generated .dtsx files are disposable and the BIML is the source of truth. Metadata-driven generation extends that one crucial step further: now even the BIML is mostly a template, and the real source of truth is your metadata — the schema, or the control table. Your platform's shape is described as data, and the pipelines are a consequence of it.

That's a genuinely different way to think about ETL, and it took me a few goes to feel it in my hands rather than just understand it in my head. But once it clicks, hand-building packages feels like hand-copying a document instead of running off prints. Why would you, when the press is right there?