Biml Is Just Templates That Finally Fight Back

When I first encountered Biml, my honest reaction was something like: "This is just an XML version of my _Templates folder, except it generates the packages instead of making me copy them." That reaction turned out to be entirely correct, and understanding it that way made Biml immediately usable in a way that treating it as a completely new paradigm would not have.

Biml — Business Intelligence Markup Language — is a DSL for describing SQL Server data integration artifacts. You write XML that describes sources, destinations, transformations, and control flow patterns. The Biml engine (BimlStudio, or the free BimlExpress plugin for SSDT) reads that XML and emits SSIS packages. The packages that come out look exactly like packages you would have built by hand, because they are built by the same SSIS API — just driven by code instead of a mouse.

The insight that unlocked it for me: the XML I was writing was the specification that my _Templates folder had always been trying to express, but in a form that the tooling could actually execute.

The Template as Code

Here is a minimal Biml example — a package that loads a flat file into a staging table:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="CM_Target"
      ConnectionString="Data Source=.;Initial Catalog=Staging;Provider=SQLNCLI11;Integrated Security=SSPI;" />
    <FlatFileConnection Name="CM_Source"
      FilePath="C:\Data\customers.csv"
      Format="Delimited" />
  </Connections>

  <Packages>
    <Package Name="Load_Customers" ConstraintMode="Linear">
      <Variables>
        <Variable Name="RunDate" DataType="DateTime"></Variable>
        <Variable Name="RowsInserted" DataType="Int32">0</Variable>
      </Variables>

      <Tasks>
        <ExecuteSQL Name="Log Start" ConnectionName="CM_Target">
          <DirectInput>
            INSERT INTO dbo.PackageLog (PackageName, RunDate, StartTime, Status)
            VALUES ('Load_Customers', CAST(GETDATE() AS DATE), SYSDATETIME(), 'Running')
          </DirectInput>
        </ExecuteSQL>

        <Dataflow Name="DFT Load Customers">
          <Transformations>
            <FlatFileSource Name="SRC Customers" ConnectionName="CM_Source" />
            <OleDbDestination Name="DST Staging" ConnectionName="CM_Target">
              <ExternalTableOutput Table="dbo.stg_Customers" />
            </OleDbDestination>
          </Transformations>
        </Dataflow>

        <ExecuteSQL Name="Log Success" ConnectionName="CM_Target">
          <DirectInput>
            UPDATE dbo.PackageLog
            SET Status = 'Success', EndTime = SYSDATETIME()
            WHERE PackageName = 'Load_Customers' AND Status = 'Running'
          </DirectInput>
        </ExecuteSQL>
      </Tasks>

      <Events>
        <Event Name="OnError" EventName="OnError">
          <Tasks>
            <ExecuteSQL Name="Log Error" ConnectionName="CM_Target">
              <DirectInput>
                UPDATE dbo.PackageLog
                SET Status = 'Failed', ErrorMessage = ?, EndTime = SYSDATETIME()
                WHERE PackageName = 'Load_Customers' AND Status = 'Running'
              </DirectInput>
              <Parameters>
                <Parameter Name="0" VariableName="System.ErrorDescription" DataType="String" Length="-1" />
              </Parameters>
            </ExecuteSQL>
          </Tasks>
        </Event>
      </Events>
    </Package>
  </Packages>
</Biml>

That XML generates a complete SSIS package with logging, an error handler, a data flow, and the structural pieces I described in the _Templates folder. The structural decisions — where the logging goes, what the variables are called, how the error handler wires up — are encoded once in the Biml. Every package generated from a Biml that includes this pattern gets the pattern correctly, every time.

What Changes When You Go From Copy-Paste to Generate

The first change is obvious: consistency. Generated packages are structurally identical because they come from the same source. The logging wires are always correct. The variable names are always the same. The error handler always captures System.ErrorDescription in the same way.

The second change is less obvious: the standard becomes auditable. The Biml file is source-controlled. When you change the standard — update the logging query, add a variable, fix the error handler — you commit the change to the Biml, and every subsequent package generation reflects it. You can diff Biml commits to see exactly what changed in the standard and when. You cannot do that with a folder of files you copy by hand.

The third change is the one that compounds: because the structural concerns are handled by the generator, the only thing you write by hand is the business-specific content. The source, the destination, the transformations. Everything else is provided. A package that would have taken an afternoon to build correctly — including the logging, the error handling, the variable setup — now takes the time to describe what it does, not how it is structured.

The Relationship to What Came Before

Biml did not require me to abandon the standards I had been building manually. It required me to express them in a form that could be executed. The _Templates folder became Biml source files. The copy-paste workflow became a generation step. The decisions stayed the same; the mechanism improved.

That continuity matters. When data engineers encounter Biml and treat it as something entirely new — a different way of thinking about SSIS — they often struggle with the learning curve because the tooling is unfamiliar. When they recognize it as the formalization of what they were already doing manually, the learning curve mostly disappears. The concepts are not new. The format is new.

If you have been copying package templates and SQL skeletons manually and have not looked at Biml yet, that folder is your Biml spec waiting to be written. The translation is more direct than you might expect. As always, I'm here to help.