Before Biml, There Was a Folder Full of Templates

There is a folder on my laptop — and its predecessors, going back farther than I want to admit — called _Templates. Inside it: a master SSIS package called MASTER_ControlFlow.dtsx. A SQL file called CREATE_TABLE_standard.sql. A file called usp_standard_skeleton.sql. A README.txt that explains what each one is for and which parts to change.

This folder predates every code generator, every automation framework, every metadata-driven approach I have built since. It was how I kept myself consistent before I had a better mechanism. It worked, mostly. It also drifted, became inconsistent with itself, and required periodic archaeology to figure out which version of which template was actually current.

That folder was not a bad idea. It was an early iteration of a correct idea.

What the Folder Actually Was

The _Templates folder was a hand-maintained specification. Every template in it encoded a decision: this is how error handling works in this codebase. This is what the audit columns are called and in what order. This is what the control flow of an SSIS package looks like before you add the business logic.

The decisions were real and defensible. The mechanism for enforcing them — manually copying files and editing them — was the weakest possible implementation. A specification that lives in a file you have to remember to look at is a suggestion, not a constraint.

But the specification itself was the valuable part. The naming conventions, the structural patterns, the decisions about what belongs in a standard package and what is business-specific — those were the intellectual work. The folder was just where that work lived while I figured out something better.

The Problems That Made "Something Better" Necessary

Version drift. When I updated a template — fixed a bug in the error handler, added a column to the standard schema, changed the logging table's column names — the update only propagated forward. Every artifact built before the update still reflected the old standard. At some point, I had enough artifacts that the "old standard" and "new standard" were effectively two different codebases that happened to serve the same purpose.

Discovery failure. The folder had to be shared to be useful across a team or even across a new engagement. Shared network paths, email attachments, source control folders — each mechanism introduced a new way for people to end up with the wrong version. I once spent an afternoon diagnosing an SSIS package problem that turned out to be a logging issue — the developer had copied from a version of the template that predated a critical fix. They had not known there was a newer version.

Partial adoption. Nobody copies a template perfectly every time. A developer skips a step. They forget to add the standard variables. They wire the error handler differently because they were not sure how the standard one worked and made their own judgment call. The template guarantees structural similarity only for people who apply it precisely — and "apply it precisely" is cognitive work that scales inversely with the number of times you have to do it.

What the Folder Was Pointing At

Every time I added something to _Templates, I was implicitly defining a rule: all artifacts of this type should have this property. The folder was a collection of rules expressed as examples. The examples were meant to be copied and modified. The rules were what mattered.

If the rules are the thing, the question becomes: can the rules be enforced rather than suggested? Can the standard structure be produced from a description rather than copied from a sample?

That is a code generation problem. The description is the input — table name, column definitions, proc parameters, source and destination for a package. The template rules are the generator. The artifact is the output. The generator applies the rules consistently, every time, without the fidelity errors that come from manual copying.

The folder was a correct intuition implemented with the wrong mechanism. The intuition was: define the standard once, apply it everywhere. The mechanism should have been: define the standard in something executable, not something copy-pasteable.

It took me longer than I'd like to admit to make that jump. But the folder was the thing that made the jump obvious once I was ready to make it.

If you still have a folder like this — and I would bet that most experienced data engineers do — it is telling you something. What it is telling you is the next post in this series. As always, I'm here to help.