Loading lesson map...

What is Biml?

Quick Overview

Business Intelligence Markup Language (Biml) is an XML-based language that generates data integration artifacts automatically. Combine Biml with C# or VB code nuggets (BimlScript) to produce hundreds of SSIS packages, T-SQL scripts, or SSAS objects from a single template and your metadata.

Key benefit: Instead of manually creating 50 SSIS packages with drag-and-drop, write one Biml template that loops through your source tables and generates all packages in seconds.

What You Can Build

• SSIS packages (.dtsx) - Data flows, control flows, event handlers
• T-SQL scripts - DDL for tables, stored procedures, indexes
• SSAS cubes - Dimensions, measures, hierarchies
• Staging layers - Automated staging table creation and load packages
• Data warehouse objects - Fact and dimension tables with consistent patterns

Your First Biml Code

Here's a complete Biml file that creates an SSIS package to load data from a source table to a staging table:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="Source" ConnectionString="Provider=SQLNCLI11;Data Source=localhost;Initial Catalog=AdventureWorks;Integrated Security=SSPI;" />
    <OleDbConnection Name="Target" ConnectionString="Provider=SQLNCLI11;Data Source=localhost;Initial Catalog=Staging;Integrated Security=SSPI;" />
  </Connections>
  <Packages>
    <Package Name="LoadCustomers" ConstraintMode="Linear">
      <Tasks>
        <ExecuteSQL Name="TruncateTarget" ConnectionName="Target">
          <DirectInput>TRUNCATE TABLE dbo.Customers</DirectInput>
        </ExecuteSQL>
        <Dataflow Name="LoadData">
          <Transformations>
            <OleDbSource Name="GetCustomers" ConnectionName="Source">
              <DirectInput>SELECT CustomerID, Name, Email FROM Sales.Customer</DirectInput>
            </OleDbSource>
            <OleDbDestination Name="InsertCustomers" ConnectionName="Target">
              <ExternalTableOutput Table="dbo.Customers" />
            </OleDbDestination>
          </Transformations>
        </Dataflow>
      </Tasks>
    </Package>
  </Packages>
</Biml>

Result: Right-click this file in Visual Studio and select "Generate SSIS Packages" to produce a fully functional SSIS package.

Core Concepts

Biml Relational Hierarchy

Biml organizes objects in a logical hierarchy:

Connections
└── Databases
    └── Schemas
        └── Tables
            └── Columns

Each level references its parent, enabling metadata-driven generation across your entire data model.

BimlScript Code Nuggets

BimlScript embeds C# or VB code within Biml using special delimiters:

Nugget Type	Syntax	Purpose	Example
Control	<# ... #>	Execute C# logic (loops, conditions)	<# foreach (var t in tables) { #>
Text	<#= ... #>	Output values into Biml	<#= table.Name #>
Class	<#+ ... #>	Define helper methods	<#+ string GetSchema() { ... } #>

Common Automation Tasks

1. Generate Staging Tables from Metadata

Read your source database schema and generate staging tables automatically:

<#@ import namespace="Varigence.Biml.CoreLowerer.SchemaManagement" #>
<#
var sourceConnection = RootNode.OleDbConnections["Source"];
var tables = sourceConnection.GetDatabaseSchema().TableNodes;
#>

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Tables>
    <# foreach (var table in tables) { #>
    <Table Name="<#= table.Name #>" SchemaName="Staging.<#= table.Schema #>">
      <Columns>
        <# foreach (var column in table.Columns) { #>
        <#= column.GetBiml() #>
        <# } #>
        <Column Name="LoadDateTime" DataType="DateTime2"/>
      </Columns>
    </Table>
    <# } #>
  </Tables>
</Biml>

2. Create Batch Data Load Packages

Generate one SSIS package per table with consistent patterns:

<#@ template tier="40" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <# foreach (var table in RootNode.Tables) { #>
    <Package Name="Load_<#= table.Schema #>_<#= table.Name #>" ConstraintMode="Linear">
      <Tasks>
        <ExecuteSQL Name="Truncate" ConnectionName="Target">
          <DirectInput>TRUNCATE TABLE [<#= table.Schema #>].[<#= table.Name #>]</DirectInput>
        </ExecuteSQL>
        <Dataflow Name="LoadData">
          <Transformations>
            <OleDbSource Name="Source" ConnectionName="Source">
              <DirectInput>SELECT * FROM [<#= table.Schema #>].[<#= table.Name #>]</DirectInput>
            </OleDbSource>
            <OleDbDestination Name="Destination" ConnectionName="Target">
              <ExternalTableOutput Table="[<#= table.Schema #>].[<#= table.Name #>]" />
            </OleDbDestination>
          </Transformations>
        </Dataflow>
      </Tasks>
    </Package>
    <# } #>
  </Packages>
</Biml>

Result: If your database has 100 tables, this generates 100 SSIS packages in one build.

3. Apply Consistent Design Patterns

Generate T-SQL statements using patterns (truncate, drop, create):

<# foreach (var table in RootNode.Tables) { #>
-- Truncate table
TRUNCATE TABLE [<#= table.Schema #>].[<#= table.Name #>];

-- Or drop and recreate
<#= table.GetDropAndCreateDdl() #>
<# } #>

Automation Benefits

Reduce Manual Coding

• One template generates packages for all tables
• Schema changes automatically reflected on rebuild
• Consistent naming conventions enforced across all objects

Improve Error Handling

• Centralized logging configuration across all packages
• Build-time validation catches errors before deployment
• Consistent error handling patterns applied uniformly

Enable CI/CD Integration

• Biml files are plain text - store in Git alongside your code
• Automated builds in Azure DevOps, Jenkins, or GitHub Actions
• Reproducible deployments from source control

Tools Overview

Tool	Use Case	Cost
BimlExpress	Free Visual Studio extension for manual builds	Free
BimlStudio	Full IDE with visual designers and debugging	Licensed
BimlFlex	Enterprise platform for modern cloud targets	Licensed

Which Tool Should You Use?

1. Learning Biml: Start with BimlExpress (free)
2. Production SSIS projects: BimlStudio for advanced features
3. Modern platforms (ADF, Databricks, Snowflake, Fabric): BimlFlex

Supported Platforms

Direct Output

• SQL Server Integration Services (SSIS) - Packages, projects, configurations
• SQL Server Analysis Services (SSAS) - Cubes, tabular models
• T-SQL Scripts - DDL, stored procedures, functions

Via BimlFlex

• Azure Data Factory - Pipelines, datasets, linked services
• Databricks - Notebooks, Delta Lake tables
• Snowflake - Stages, tables, stored procedures
• Microsoft Fabric - Lakehouses, warehouses, pipelines

Next Steps

1. Quick Start Guide - Build your first Biml solution in 5 minutes
2. Biml Basics - Core syntax and conventions
3. Biml for SSIS - Package development patterns
4. Build Staging Layer - End-to-end example