Techniques for Automating Biml Code

This page covers alternative methods to automate Biml code generation. These approaches are useful when you have existing metadata in specific formats or prefer tools outside the Biml ecosystem.

Key benefits of Biml code (regardless of how it's generated):

Works well with source control
Consistent across relational, SSIS, and SSAS
Easy to manage with find/replace
Simpler code reviews

Recommended Approach

For most scenarios, BimlScript is the recommended automation method. It provides the most flexibility and integrates directly with Biml tools. See Automation and Patterns for comprehensive BimlScript examples including metadata-driven development, CI/CD integration, and platform-specific patterns.

Choosing an Automation Method

Method	Best For	Complexity	When to Use
BimlScript	Most scenarios	Low-Medium	Full automation with logic, loops, conditionals
Excel	Existing data dictionaries	Low	Quick metadata translation from spreadsheets
SQL Scripts	Database-stored metadata	Medium	T-SQL expertise, metadata already in SQL Server
PowerShell	CI/CD automation	Medium	Existing PowerShell infrastructure
.NET Program	Complex logic	High	API integration, custom business rules
XSLT	Existing XML metadata	High	XML transformation expertise

BimlScript (Recommended)

BimlScript embeds C# or VB code directly into your Biml XML using code nuggets. This is the most powerful and flexible approach because it:

Accesses database metadata directly with GetDatabaseSchema()
Supports loops, conditionals, and variables
Integrates with CI/CD pipelines
Works with all Biml tools (BimlExpress, BimlStudio, BimlFlex)

Simple example:

<#@ template tier="10" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Tables>
    <# foreach (var tableName in new[] {"Customer", "Product", "Order"}) { #>
    <Table Name="<#= tableName #>" SchemaName="stg" />
    <# } #>
  </Tables>
</Biml>

Learn more:

Automation and Patterns - Metadata-driven development, CI/CD, staging patterns
Introduction to BimlScript - Code nugget syntax and directives
Common Patterns - Reusable patterns for staging, incremental loads, error handling

Platform-specific documentation:

Biml Basics for SSIS - SQL Server Integration Services
BimlFlex for ADF - Azure Data Factory
BimlFlex for Databricks - Databricks
BimlFlex for Snowflake - Snowflake
BimlFlex for Fabric - Microsoft Fabric

Excel-Based Automation

Many organizations store metadata in Excel workbooks (data dictionaries, mapping documents). You can use Excel formulas to generate Biml XML.

When to Use

Metadata already exists in Excel
Non-technical team members maintain the metadata
Quick one-time generation needed

Example: Generate Table Definitions

Excel setup:

A (Table Name)	B (Schema)	C (Generated Biml)
Customer	stg	`<Table Name="Customer" SchemaName="stg"/>`
Product	stg	`<Table Name="Product" SchemaName="stg"/>`

Formula in column C:

=CONCATENATE("<Table Name=""", A2, """ SchemaName=""", B2, """/>")

Example: Generate Column Definitions

For a more complex example with columns:

=CONCATENATE("<Column Name=""", A2, """ DataType=""", B2, """ Length=""", C2, """/>")

Workflow

Set up Excel template with metadata columns
Add formula columns to generate Biml XML
Copy generated XML from Excel
Paste into a .biml file
Wrap with <Biml> root element
Build in BimlExpress or BimlStudio

SQL Script Automation

If your metadata lives in a database, SQL scripts can generate Biml XML using string concatenation or XML functions.

When to Use

Metadata stored in SQL Server tables
Strong T-SQL skills on the team
Need to version control the generation scripts

Example: Using FOR XML PATH

Generate table definitions from INFORMATION_SCHEMA:

DECLARE @BimlOutput NVARCHAR(MAX);

SELECT @BimlOutput = (
    SELECT
        '<Table Name="' + TABLE_NAME + '" SchemaName="stg">' +
        (
            SELECT '<Column Name="' + COLUMN_NAME +
                   '" DataType="' +
                   CASE DATA_TYPE
                       WHEN 'int' THEN 'Int32'
                       WHEN 'bigint' THEN 'Int64'
                       WHEN 'varchar' THEN 'String'
                       WHEN 'nvarchar' THEN 'String'
                       WHEN 'datetime' THEN 'DateTime'
                       WHEN 'decimal' THEN 'Decimal'
                       ELSE 'String'
                   END +
                   CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
                        THEN '" Length="' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR)
                        ELSE ''
                   END +
                   '"/>'
            FROM INFORMATION_SCHEMA.COLUMNS c
            WHERE c.TABLE_NAME = t.TABLE_NAME
              AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
            ORDER BY ORDINAL_POSITION
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') +
        '</Table>'
    FROM INFORMATION_SCHEMA.TABLES t
    WHERE TABLE_TYPE = 'BASE TABLE'
      AND TABLE_SCHEMA = 'dbo'
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)');

-- Wrap with Biml root element
SET @BimlOutput = '<Biml xmlns="http://schemas.varigence.com/biml.xsd"><Tables>' +
                  @BimlOutput +
                  '</Tables></Biml>';

-- Output result
SELECT @BimlOutput AS BimlCode;

Tip: Store Biml in SQL Server

SQL Server's XML data type can store and query Biml directly:

-- Store Biml in a table
CREATE TABLE BimlTemplates (
    TemplateName NVARCHAR(100),
    BimlCode XML
);

-- Query specific elements
SELECT BimlCode.query('//Table[@Name="Customer"]')
FROM BimlTemplates
WHERE TemplateName = 'Staging';

PowerShell Automation

PowerShell is useful for automation scenarios, especially when integrating Biml generation into CI/CD pipelines.

When to Use

Existing PowerShell infrastructure
CI/CD pipeline integration
Need to combine multiple data sources

Example: Generate Tables from SQL Server

# Connect to SQL Server and generate Biml
param(
    [string]$Server = "localhost",
    [string]$Database = "AdventureWorks",
    [string]$OutputPath = "generated-tables.biml"
)

# Import SQL Server module
Import-Module SqlServer

# Query table metadata
$query = @"
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
"@

$tables = Invoke-Sqlcmd -ServerInstance $Server -Database $Database -Query $query

# Build Biml XML
$biml = @"
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Tables>
"@

foreach ($table in $tables) {
    $schema = $table.TABLE_SCHEMA
    $name = $table.TABLE_NAME
    $biml += "    <Table Name=`"$name`" SchemaName=`"stg`">`n"
    $biml += "      <Annotations>`n"
    $biml += "        <Annotation Tag=`"SourceSchema`">$schema</Annotation>`n"
    $biml += "        <Annotation Tag=`"SourceTable`">$name</Annotation>`n"
    $biml += "      </Annotations>`n"
    $biml += "    </Table>`n"
}

$biml += @"
  </Tables>
</Biml>
"@

# Save to file
$biml | Out-File -FilePath $OutputPath -Encoding UTF8

Write-Host "Generated Biml for $($tables.Count) tables: $OutputPath"

CI/CD Integration

Use PowerShell in Azure DevOps or GitHub Actions:

# Azure DevOps example
- task: PowerShell@2
  displayName: 'Generate Biml from metadata'
  inputs:
    targetType: 'filePath'
    filePath: 'scripts/Generate-Biml.ps1'
    arguments: '-Server $(SqlServer) -Database $(Database) -OutputPath $(Build.SourcesDirectory)/generated.biml'

.NET Program Automation

For complex scenarios requiring custom business logic, a .NET program offers maximum flexibility.

When to Use

Complex conditional logic
Integration with external APIs
Custom metadata transformations
Need to use the Biml API directly

Example: Using XDocument

using System;
using System.Collections.Generic;
using System.Xml.Linq;

public class BimlGenerator
{
    public static void Main()
    {
        // Sample metadata (could come from database, API, etc.)
        var tables = new List<(string Name, string Schema)>
        {
            ("Customer", "Sales"),
            ("Product", "Production"),
            ("Order", "Sales")
        };

        // Build Biml using XDocument
        XNamespace ns = "http://schemas.varigence.com/biml.xsd";

        var biml = new XDocument(
            new XElement(ns + "Biml",
                new XElement(ns + "Tables",
                    tables.Select(t =>
                        new XElement(ns + "Table",
                            new XAttribute("Name", t.Name),
                            new XAttribute("SchemaName", "stg"),
                            new XElement(ns + "Annotations",
                                new XElement(ns + "Annotation",
                                    new XAttribute("Tag", "SourceSchema"),
                                    t.Schema),
                                new XElement(ns + "Annotation",
                                    new XAttribute("Tag", "SourceTable"),
                                    t.Name)))))));

        // Save to file
        biml.Save("generated-tables.biml");

        Console.WriteLine($"Generated Biml for {tables.Count} tables");
    }
}

Using the Biml API (BimlStudio SDK)

If you have BimlStudio, you can use the Biml API to create objects programmatically:

using Varigence.Biml;
using Varigence.Biml.Extensions;

// Create a table using the API
var table = new AstTableNode
{
    Name = "Customer",
    SchemaName = "stg"
};

table.Columns.Add(new AstTableColumnNode
{
    Name = "CustomerID",
    DataType = System.Data.DbType.Int32,
    IsNullable = false
});

table.Columns.Add(new AstTableColumnNode
{
    Name = "CustomerName",
    DataType = System.Data.DbType.String,
    Length = 100
});

// Get the Biml XML representation
string bimlXml = table.GetBiml();

XSLT Transformation

For organizations with existing XML metadata and XSLT expertise, transforming XML to Biml is a viable option.

When to Use

Existing metadata in XML format
Strong XSLT skills on the team
Need to maintain transformation rules in declarative format

Example: Basic XSLT

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="xml" indent="yes"/>

  <xsl:template match="/metadata">
    <Biml xmlns="http://schemas.varigence.com/biml.xsd">
      <Tables>
        <xsl:apply-templates select="table"/>
      </Tables>
    </Biml>
  </xsl:template>

  <xsl:template match="table">
    <Table Name="{@name}" SchemaName="stg"/>
  </xsl:template>
</xsl:stylesheet>

Copy/Paste and Editor Macros

For simple scenarios, code snippets and editor macros provide quick results without automation overhead.

When to Use

Small projects with few objects
One-time generation needs
Learning Biml syntax

Tips

Create a snippet library for common patterns
Use Biml's convention over configuration to keep snippets minimal
Consider graduating to BimlScript as complexity grows

Next Steps

Automation and Patterns - Master BimlScript with metadata-driven development, CI/CD integration, and platform-specific patterns
Introduction to BimlScript - Learn code nugget syntax and directives
Common Patterns - Reusable patterns for staging, incremental loads, error handling
Biml Basics for SSIS - Generate SSIS packages

Choosing an Automation Method​

BimlScript (Recommended)​

Excel-Based Automation​

When to Use​

Example: Generate Table Definitions​

Example: Generate Column Definitions​

Workflow​

SQL Script Automation​

When to Use​

Example: Using FOR XML PATH​

Tip: Store Biml in SQL Server​

PowerShell Automation​

When to Use​

Example: Generate Tables from SQL Server​

CI/CD Integration​

.NET Program Automation​

When to Use​

Example: Using XDocument​

Using the Biml API (BimlStudio SDK)​

XSLT Transformation​

When to Use​

Example: Basic XSLT​

Copy/Paste and Editor Macros​

When to Use​

Tips​

Next Steps​

Choosing an Automation Method

BimlScript (Recommended)

Excel-Based Automation

When to Use

Example: Generate Table Definitions

Example: Generate Column Definitions

Workflow

SQL Script Automation

When to Use

Example: Using FOR XML PATH

Tip: Store Biml in SQL Server

PowerShell Automation

When to Use

Example: Generate Tables from SQL Server

CI/CD Integration

.NET Program Automation

When to Use

Example: Using XDocument

Using the Biml API (BimlStudio SDK)

XSLT Transformation

When to Use

Example: Basic XSLT

Copy/Paste and Editor Macros

When to Use

Tips

Next Steps