A month into my first proper ETL job, I was handed a task that sounded simple and turned out to be soul-destroying: build a staging load for every table in a source system. Not two or three. Dozens. Each package near-identical to the last — truncate the staging table, pull from source, push to staging — differing only in the table name. The kind of work where by package eleven you've stopped thinking, and by package forty you've introduced a subtle bug you won't find until a Tuesday three weeks from now.
I did about six of them by hand before I went looking for a better way. What I found was BIML, and it changed how I think about building anything repetitive. So this is the post I wish someone had handed me in week one.
Death by copy-paste
Let me describe the problem properly, because if you've not lived it, "build some SSIS packages" sounds fine.
SSIS packages are, under the hood, XML files — .dtsx — but you don't write that XML by hand. You drag boxes around in Visual Studio: a data flow here, an Execute SQL task there, wire up the connections, map the columns. For one package that's pleasant enough. The trouble is that a real data platform doesn't need one package. It needs one per table, and they're all 95% the same.
So you build the first one carefully, then you copy-paste it and change the bits that differ. And copy-paste is where consistency goes to die. Someone forgets to update a table name. Someone tweaks the error handling on package twenty but not the other fifty-nine. Six months later nobody can tell you whether all your loads actually behave the same way, because a human built each one slightly differently on a slightly different afternoon.
The manual approach doesn't just cost time. It manufactures a class of bug that's almost impossible to audit your way out of — which, in a financial-data shop, is not a small thing.
What BIML actually is
BIML stands for Business Intelligence Markup Language, and the one-sentence version is: it's a template language that writes your SSIS packages for you.
You describe your package once, in a tidy bit of XML, and BIML expands that into the .dtsx files SSIS actually runs. On its own that's mildly useful. The moment it becomes magic is when you mix in a little C# — that flavour is called BimlScript — so your template can loop. Write the pattern once, hand it a list of tables, and it emits a correct, identical package for every single one.
To follow along you need BimlExpress, the free add-in for Visual Studio (it's the successor to the old BIDS Helper BIML feature, which you should no longer use). Install it, add a BIML file to your project, and you get a "Generate SSIS Packages" option on the right-click menu. That's the whole toolchain to start.
The smallest useful example
Here's a single load package expressed in BIML — two connections, a truncate, and a data flow:
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>
<OleDbConnection Name="Source" ConnectionString="Provider=SQLNCLI11;Server=.;Initial Catalog=SourceDB;Integrated Security=SSPI;"/>
<OleDbConnection Name="Staging" ConnectionString="Provider=SQLNCLI11;Server=.;Initial Catalog=Staging;Integrated Security=SSPI;"/>
</Connections>
<Packages>
<Package Name="Load_Customer" ConstraintMode="Linear">
<Tasks>
<ExecuteSQL Name="Truncate Staging" ConnectionName="Staging">
<DirectInput>TRUNCATE TABLE stg.Customer;</DirectInput>
</ExecuteSQL>
<Dataflow Name="Copy Customer">
<Transformations>
<OleDbSource Name="src" ConnectionName="Source">
<DirectInput>SELECT * FROM dbo.Customer;</DirectInput>
</OleDbSource>
<OleDbDestination Name="dst" ConnectionName="Staging">
<ExternalTableOutput Table="stg.Customer"/>
</OleDbDestination>
</Transformations>
</Dataflow>
</Tasks>
</Package>
</Packages>
</Biml>Right-click, generate, and you get Load_Customer.dtsx — a real package you could open and run. Fine. But we've not saved any effort yet; we've just typed XML instead of dragging boxes.
Making it metadata-driven
Now the good part. Instead of writing that block sixty times, we wrap the package in a loop and let a list drive it:
<#@ template tier="2" #>
<#
var tables = new [] { "Customer", "Invoice", "Payment", "Account", "Contact" };
#>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Packages>
<# foreach (var table in tables) { #>
<Package Name="Load_<#= table #>" ConstraintMode="Linear">
<Tasks>
<ExecuteSQL Name="Truncate" ConnectionName="Staging">
<DirectInput>TRUNCATE TABLE stg.<#= table #>;</DirectInput>
</ExecuteSQL>
<Dataflow Name="Copy <#= table #>">
<Transformations>
<OleDbSource Name="src" ConnectionName="Source">
<DirectInput>SELECT * FROM dbo.<#= table #>;</DirectInput>
</OleDbSource>
<OleDbDestination Name="dst" ConnectionName="Staging">
<ExternalTableOutput Table="stg.<#= table #>"/>
</OleDbDestination>
</Transformations>
</Dataflow>
</Tasks>
</Package>
<# } #>
</Packages>
</Biml>Two kinds of C# "nugget" are doing the work here. The control nuggets — <# ... #> — run logic, like the foreach. The expression nuggets — <#= ... #> — drop a value into the output, like the table name. Generate that, and five packages appear at once, byte-for-byte consistent apart from the names.
And you don't have to hardcode that array. You can point BimlScript at your source database and let it read the table list straight from the schema, or drive it from a small control table you maintain in SQL — SELECT table_name, load_type FROM etl.SourceTables. At that point adding a new table to your platform means adding a row, not building a package. The generation is the same whether the list has five names or five hundred. That was the coffee moment for me: I kicked off a generate, went to refill my mug, and came back to sixty packages that were all, provably, the same.
The mindset shift that matters more than the syntax
Here's the part that trips people up, and it's not technical — it's a change in what you treat as "the real thing."
Once you generate packages from BIML, the .dtsx files are disposable. The BIML is the source of truth. The packages are just output.
That means two rules you have to actually internalise. Never hand-edit a generated package — your change will evaporate the next time anyone regenerates. And put the BIML in source control, not the packages. If you find yourself opening a .dtsx to "just quickly fix one thing," stop: the fix belongs in the template, so that every package inherits it. The whole value of this approach is that all your loads are identical by construction. The first time someone tweaks one package by hand, you've quietly reintroduced exactly the drift you adopted BIML to kill.
Gotchas — the 3am checklist
A few things that cost me an evening each so they don't cost you one:
- Tiers run in order. The
<#@ template tier="2" #>directive controls when a script runs relative to others. If you gather metadata in one file and emit packages in another, the metadata has to run first (a lower tier). Get the order wrong and your loop runs over an empty list and silently produces nothing. - Don't bake connection strings in. You'll deploy across dev, test and production, and hardcoded strings will follow you into an outage. Generate project parameters or package configurations for connections — let BIML build those too.
- Mind your nugget types.
<# #>runs code;<#= #>prints a value. Swapping them is the single most common source of "why is my package name literally<#= table #>" confusion. - Regenerate everything, every time. After you improve the template, regenerate the whole set so nothing lags a version behind. Treat a stale package like stale code.
- BimlExpress, not BIDS Helper. The old BIDS Helper BIML support is retired; use BimlExpress in Visual Studio 2015 or 2017. If you outgrow the free add-in — multiple tiers, big metadata models — that's when BimlStudio starts earning its licence.
I came into this job from a background where "data" meant marketing analytics and Python, not enterprise ETL, so I had no pride invested in doing packages the manual way. Maybe that helped — I saw the drudgery for what it was and went looking for the exit. If you're a month into something similar and staring down a folder of near-identical packages you're expected to build by hand, this is the exit. Not because it's clever, but because it deletes a whole category of mistake you'd otherwise spend the next year finding one bug at a time.
This picks up from my first weeks as an ETL developer — where I mostly just tried to keep up.