Loading lesson map...

Biml Basics for Data Engineers

Why Biml Matters for Pipeline Development

Building ETL pipelines manually is time-consuming and error-prone:

• Creating 50 staging tables means writing 50 nearly-identical SSIS packages
• A column rename requires updating multiple packages by hand
• Inconsistent error handling across packages causes debugging headaches
• Testing and deploying changes takes hours instead of minutes

Biml solves this by generating pipelines from metadata:

• Define a pattern once, apply it to all tables automatically
• Change metadata, regenerate all pipelines instantly
• Enforce consistent patterns across your entire solution
• Integrate with CI/CD for automated builds and deployments

Time savings example: A 100-table staging layer that takes 2-3 weeks to build manually can be generated in 1-2 hours with Biml.

Core Biml Concepts

Biml files are XML documents that conform to the Biml schema. If you know XML, you just need to learn a few conventions to start generating SSIS packages.

The Biml File Structure

Every Biml file starts with the root element and namespace declaration:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <!-- Your Biml content goes here -->
</Biml>

Most Biml tools auto-generate this declaration, so you don't need to memorize it.

Collections Use Wrapper Elements

When adding objects to a Biml file, wrap them in a collection element. This pattern applies throughout Biml:

Collection Element	Child Elements
<Packages>	<Package>
<Connections>	<OleDbConnection>, <AdoNetConnection>
<Tables>	<Table>
<Tasks>	<Dataflow>, <ExecuteSQL>, <Container>

Example: Adding a package requires the <Packages> wrapper:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <Package Name="MyPackage">
      <Tasks>
        <Dataflow Name="LoadData" />
      </Tasks>
    </Package>
  </Packages>
</Biml>

Referencing Objects by Name

Biml uses name-based references to connect objects. Attribute names ending with Name indicate a reference:

• ConnectionName="SourceDB" - references a connection named "SourceDB"
• VariableName="User.Counter" - references a variable (with namespace prefix)
• TableName="Sales.Customer" - references a table

Scoped names resolve ambiguity when multiple objects share a name. Like SQL Server's multi-part names (Server.Database.Schema.Table), Biml uses dot notation:

<!-- Reference a specific variable when duplicates exist -->
<ForEachFromVariableLoop Name="Loop1"
  VariableName="MyPackage.User.LoopVariable">

The Biml compiler finds the closest matching object by default, so you only need scoped names when disambiguation is required.

Your First SSIS Package

Copy this code into a new .biml file in your Visual Studio project with BimlExpress installed:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <Package Name="HelloBiml" ProtectionLevel="EncryptSensitiveWithUserKey">
      <Tasks>
        <ExecuteSQL Name="LogStart" ConnectionName="TargetDB">
          <DirectInput>
            PRINT 'Package started at ' + CONVERT(VARCHAR, GETDATE(), 120)
          </DirectInput>
        </ExecuteSQL>
      </Tasks>
    </Package>
  </Packages>

  <Connections>
    <OleDbConnection Name="TargetDB"
      ConnectionString="Provider=SQLNCLI11;Server=localhost;Initial Catalog=master;Integrated Security=SSPI;" />
  </Connections>
</Biml>

To build:

1. Right-click the .biml file in Solution Explorer
2. Select Generate SSIS Packages
3. The HelloBiml.dtsx package appears in your project

Generating Multiple Packages with BimlScript

The real power of Biml comes from BimlScript - embedded C# code that generates Biml dynamically. This example creates a package for each table in a list:

<#@ template language="C#" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <#
    // Define your source tables
    var tables = new[] { "Customer", "Product", "Order", "OrderDetail" };

    foreach (var tableName in tables) {
  #>
    <Package Name="Load_<#=tableName#>" ProtectionLevel="EncryptSensitiveWithUserKey">
      <Tasks>
        <Dataflow Name="DFT_<#=tableName#>">
          <Transformations>
            <OleDbSource Name="SRC_<#=tableName#>" ConnectionName="SourceDB">
              <DirectInput>SELECT * FROM dbo.<#=tableName#></DirectInput>
            </OleDbSource>
            <OleDbDestination Name="DST_<#=tableName#>" ConnectionName="TargetDB">
              <ExternalTableOutput Table="dbo.<#=tableName#>" />
            </OleDbDestination>
          </Transformations>
        </Dataflow>
      </Tasks>
    </Package>
    <# } #>
  </Packages>

  <Connections>
    <OleDbConnection Name="SourceDB"
      ConnectionString="Provider=SQLNCLI11;Server=SourceServer;Initial Catalog=SourceDB;Integrated Security=SSPI;" />
    <OleDbConnection Name="TargetDB"
      ConnectionString="Provider=SQLNCLI11;Server=TargetServer;Initial Catalog=TargetDB;Integrated Security=SSPI;" />
  </Connections>
</Biml>

Result: One build generates four SSIS packages: Load_Customer.dtsx, Load_Product.dtsx, Load_Order.dtsx, and Load_OrderDetail.dtsx.

BimlScript Code Nugget Types

Nugget	Syntax	Purpose
Control	<# code #>	Execute C# logic (loops, conditionals)
Text	<#= expression #>	Output a value into the Biml
Directive	<#@ directive #>	Configure template settings

Beyond SSIS: Cloud Platform Support

While this guide focuses on SSIS package generation using BimlExpress and BimlStudio, the Biml ecosystem extends to modern cloud data platforms through BimlFlex:

• Azure Data Factory - JSON pipeline generation
• Databricks - Notebooks and Delta Lake DDL
• Snowflake - Stored procedures and tasks
• Microsoft Fabric - Lakehouse pipelines

BimlFlex provides a metadata-driven approach with pre-built patterns for data vault, dimensional modeling, and staging layers across all these platforms. For cloud platform automation, see the BimlFlex documentation.

Practical Automation Benefits

Reducing Manual Coding

Approach	Time for 50-Table Staging Layer
Manual SSIS design	100-150 hours (2-3 hours per package)
Biml automation	~2 hours (template + generation)
Time saved	98%

Improving Error Handling Consistency

Define error handling once in your Biml template:

<Package Name="Load_<#=tableName#>">
  <Events>
    <Event EventType="OnError" Name="OnError">
      <Tasks>
        <ExecuteSQL Name="LogError" ConnectionName="LogDB">
          <DirectInput>
            INSERT INTO ErrorLog (PackageName, ErrorTime, ErrorMessage)
            VALUES ('<#=tableName#>', GETDATE(), ?)
          </DirectInput>
        </ExecuteSQL>
      </Tasks>
    </Event>
  </Events>
  <!-- Package tasks... -->
</Package>

Every generated package includes identical error handling - no forgotten logging calls or inconsistent patterns.

CI/CD Integration

Biml projects integrate with standard build pipelines:

# Azure DevOps example
- task: MSBuild@1
 displayName: 'Build Biml Solution'
 inputs:
  solution: '**/*.mst'
  msbuildArguments: '/p:OutputPath=$(Build.ArtifactStagingDirectory)/SSIS'

- task: PublishBuildArtifacts@1
 inputs:
  pathToPublish: '$(Build.ArtifactStagingDirectory)/SSIS'
  artifactName: 'SSISPackages'

Benefits:

• Version control your Biml templates (not generated packages)
• Automated package generation on every commit
• Consistent builds across development, test, and production

Key Conventions Summary

Convention	Description
Collection wrappers	<Packages> contains <Package>, <Tasks> contains task elements
Name references	Attributes ending in Name reference other objects
Scoped names	Use dot notation for disambiguation: Package.Task.Variable
Required properties	Most properties are optional; Name is almost always required

Next Steps

Now that you understand Biml basics, continue with these guides:

• Biml for Relational Databases - Connections, tables, columns, and keys
• Biml for SSIS - Complete SSIS package development
• Common Patterns - Reusable patterns for staging, incremental loads, and error handling
• Automation and Patterns - Metadata-driven development philosophy
• Troubleshooting Guide - Debugging tips and common errors

Learning Resources

• IntelliSense: BimlExpress and BimlStudio provide autocomplete for all Biml elements
• Import existing packages: Use BimlStudio to reverse-engineer SSIS packages into Biml
• API Reference: Complete Biml language documentation at docs.varigence.com/biml-reference/language-reference