Introduction to BimlScript

BimlScript embeds C# or VB code directly into Biml XML, transforming static templates into dynamic generators. Instead of writing 100 nearly-identical SSIS packages by hand, write one BimlScript template that loops through your metadata and generates all packages automatically.

What You'll Learn

• The 5 code nugget types and when to use each
• How to generate multiple packages from a single template
• Essential directives for controlling compilation
• Code reuse patterns (Include, CallBimlScript)
• Common mistakes and how to avoid them

Prerequisites

• Basic understanding of Biml syntax
• Familiarity with C# fundamentals (see C# Primer)
• BimlExpress or BimlStudio installed

---

Why BimlScript Matters

The Manual Approach Problem

Creating ETL packages manually doesn't scale:

Tables	Manual Time	BimlScript Time	Savings
10	20-30 hours	1-2 hours	90%+
50	100-150 hours	2-3 hours	98%+
100	200-300 hours	3-4 hours	98%+

Beyond Time Savings

• Consistency: Every package follows the same pattern
• Maintainability: Change the template, regenerate all packages
• Error Reduction: No copy-paste mistakes across packages
• Version Control: Track changes to templates, not generated artifacts

---

The 5 Code Nugget Types

BimlScript uses code nuggets—snippets of C# or VB embedded in Biml XML. Each nugget type serves a specific purpose.

Nugget	Syntax	Purpose
Control	<# code #>	Execute logic (loops, conditions, variables)
Text	<#= expr #>	Output expression value as text
Class	<#+ code #>	Define reusable methods and classes
Comment	<#* text *#>	Compiler-ignored comments
Directive	<#@ dir #>	Compiler instructions (tier, imports)

---

Control Nuggets

Control nuggets execute C# or VB code that controls the generation flow. Use them for:

• Declaring variables
• Loops (generating multiple objects)
• Conditionals (including/excluding based on criteria)
• Accessing external data sources

Syntax

<# C# or VB code here #>

Example: Generate One Package Per Table

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

Key points:

• The foreach loop iterates through all tables in the Biml model
• Each iteration generates a complete <Package> element
• The open brace { and close brace } span multiple nuggets

Example: Conditional Logic

<# var emitPackages = true; #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <# if (emitPackages) { #>
  <Packages>
    <Package Name="ConditionalPackage">
      <Tasks>
        <ExecuteSQL Name="RunSQL" ConnectionName="Target">
          <DirectInput>SELECT 1</DirectInput>
        </ExecuteSQL>
      </Tasks>
    </Package>
  </Packages>
  <# } #>
</Biml>

---

Text Nuggets

Text nuggets evaluate an expression and insert the result as text. Use them to:

• Insert dynamic values (table names, column names)
• Build connection strings from variables
• Generate SQL statements with metadata

Syntax

<#= expression #>

Example: Dynamic Package Naming

<Package Name="STG_<#= table.Schema.Name #>_<#= table.Name #>">

Output: STG_Sales_Customer

Example: Generate Column List

<DirectInput>
  SELECT
    <#= string.Join(",\n    ", table.Columns.Select(c => $"[{c.Name}]")) #>
  FROM [<#= table.Schema.Name #>].[<#= table.Name #>]
</DirectInput>

Output:

SELECT
    [CustomerID],
    [FirstName],
    [LastName],
    [Email]
FROM [Sales].[Customer]

Example: Mix Text Nuggets with Static Text

<Package Name="Load_<#= table.Name #>_<#= DateTime.Now.ToString("yyyyMMdd") #>">

Output: Load_Customer_20241215

---

Class Nuggets

Class nuggets define reusable methods and properties that can be called from other nuggets within the same file. Use them to:

• Centralize repeated logic
• Create utility functions
• Improve code readability

Syntax

<#+
  // Method and property definitions here
#>

Example: Utility Methods

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <Package Name="<#= GetUniqueName("Staging") #>">
      <Tasks>
        <ExecuteSQL Name="Log_<#= GetFormattedDate() #>" ConnectionName="Target">
          <DirectInput>SELECT 1</DirectInput>
        </ExecuteSQL>
      </Tasks>
    </Package>
  </Packages>
</Biml>

<#+
  public string GetUniqueName(string baseName)
  {
    return baseName + "_" + Guid.NewGuid().ToString("N").Substring(0, 8);
  }

  public string GetFormattedDate()
  {
    return DateTime.Now.ToString("yyyyMMdd_HHmmss");
  }
#>

Output: Staging_a1b2c3d4

Scope: Class nuggets are only available within the file where they are defined. For cross-file reuse, use the code directive or see Biml Utility Methods.

---

Comment Nuggets

Comment nuggets are ignored during compilation. Use them to:

• Document your BimlScript logic
• Temporarily disable code sections
• Leave notes for other developers

Syntax

<#*
  Multiline comment content here.
  This entire block is ignored during compilation.
*#>

Example

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <#*
    TODO: Add error handling for empty table collections
    Author: Data Engineering Team
    Last Modified: 2024-01-15
  *#>
  <Packages>
    <# foreach (var table in RootNode.Tables) { #>
      <#* Skip system tables - uncomment when ready:
      if (table.Name.StartsWith("sys")) continue;
      *#>
      <Package Name="Load_<#= table.Name #>">
        <Tasks />
      </Package>
    <# } #>
  </Packages>
</Biml>

Tip: Comment nuggets are different from XML comments (<!-- -->). XML comments appear in the generated output; BimlScript comments do not.

---

Directives

Directives provide instructions to the Biml compiler. They control compilation order, import namespaces, and enable code reuse.

Syntax

<#@ directiveName attribute="value" #>

Essential Directives

Directive	Purpose	Example
template	Set tier, language	<#@ template tier="20" #>
import	Add namespace	<#@ import namespace="System.Linq" #>
include	Insert file contents	<#@ include file="Fragment.biml" #>
property	Define CallBimlScript parameter	<#@ property name="table" type="AstTableNode" #>
code	Reference external .cs/.vb file	<#@ code file="Helpers.cs" #>
assembly	Reference external DLL	<#@ assembly name="MyLibrary.dll" #>

template Directive Options

• tier: Compilation order (lower compiles first)
• language: C# (default) or VB

<#@ template tier="20" language="C#" #>

import Directive

Import .NET namespaces to use types without full qualification:

<#@ import namespace="System.Linq" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="Varigence.Biml.CoreLowerer.SchemaManagement" #>

---

Complete Example: Staging Layer Generator

This example demonstrates a complete BimlScript solution that:

1. Defines connections
2. Imports metadata from a source database
3. Creates load packages for each table

File 1: Connections (Tier 10)

<!-- 1-Connections.biml -->
<#@ template tier="10" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="Source"
      ConnectionString="Provider=SQLNCLI11;Server=SourceServer;Initial Catalog=AdventureWorks;Integrated Security=SSPI;" />
    <OleDbConnection Name="Target"
      ConnectionString="Provider=SQLNCLI11;Server=TargetServer;Initial Catalog=Staging;Integrated Security=SSPI;" />
  </Connections>
</Biml>

File 2: Import Metadata (Tier 20)

<!-- 2-ImportTables.biml -->
<#@ template tier="20" #>
<#@ import namespace="Varigence.Biml.CoreLowerer.SchemaManagement" #>
<#
var sourceConnection = RootNode.OleDbConnections["Source"];
var tables = sourceConnection.GetDatabaseSchema(
    new List<string>{"Sales", "Production"},
    null,
    ImportOptions.ExcludeViews | ImportOptions.ExcludeForeignKey
).TableNodes;
#>

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

File 3: Generate Packages (Tier 30)

<!-- 3-LoadPackages.biml -->
<#@ template tier="30" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <# foreach (var table in RootNode.Tables) {
       var sourceSchema = table.GetTag("SourceSchema");
    #>
    <Package Name="Load_<#= table.Name #>" ConstraintMode="Linear">
      <Tasks>
        <ExecuteSQL Name="Truncate" ConnectionName="Target">
          <DirectInput>TRUNCATE TABLE [stg].[<#= table.Name #>]</DirectInput>
        </ExecuteSQL>
        <Dataflow Name="LoadData">
          <Transformations>
            <OleDbSource Name="Source" ConnectionName="Source">
              <DirectInput>SELECT * FROM [<#= sourceSchema #>].[<#= table.Name #>]</DirectInput>
            </OleDbSource>
            <DerivedColumns Name="AddAudit">
              <Columns>
                <Column Name="LoadDateTime" DataType="DateTime2" Length="8">
                  (DT_DBTIMESTAMP2,7)GETDATE()
                </Column>
              </Columns>
            </DerivedColumns>
            <OleDbDestination Name="Destination" ConnectionName="Target">
              <ExternalTableOutput Table="[stg].[<#= table.Name #>]" />
            </OleDbDestination>
          </Transformations>
        </Dataflow>
      </Tasks>
    </Package>
    <# } #>
  </Packages>
</Biml>

Build Order

1. Select all three .biml files in Solution Explorer
2. Right-click > Generate SSIS Packages
3. BimlScript compiles files in tier order (10 → 20 → 30)
4. Generated packages appear in your SSIS project

---

Understanding the Tier System

Biml files compile in tier order (lowest first). Use tiers to ensure objects exist before they're referenced.

Why Tiers Matter

Tier 10: Connections compile first
Tier 20: Tables can reference connections
Tier 30: Packages can reference tables and connections

Without proper tiers, you'll encounter "object not found" errors.

Recommended Tier Strategy

Tier Range	Content Type
1-10	Connections, file formats
11-20	Databases, schemas, tables
21-30	Views, stored procedures
31-40	Individual packages
41-50	Master workflow packages

Tip: Use increments of 10 to leave room for inserting files between tiers.

---

Code Reuse Patterns

Include Directive

The include directive copies file contents directly into your BimlScript (like copy-paste at compile time).

Main file:

<Package Name="MyPackage">
  <Variables>
    <#@ include file="StandardVariables.biml" #>
  </Variables>
</Package>

StandardVariables.biml:

<Variable Name="RowCount" DataType="Int32">0</Variable>
<Variable Name="ErrorCount" DataType="Int32">0</Variable>

Use Include when: The fragment is static and used in multiple places.

CallBimlScript

CallBimlScript passes parameters to a callable file (like a function call).

Caller:

<Packages>
  <# foreach (var table in RootNode.Tables) { #>
  <#= CallBimlScript("PackageTemplate.biml", table, "Target") #>
  <# } #>
</Packages>

PackageTemplate.biml (callee):

<#@ property name="table" type="AstTableNode" #>
<#@ property name="connectionName" type="String" #>

<Package Name="Load_<#= table.Name #>">
  <Tasks>
    <Dataflow Name="LoadData">
      <Transformations>
        <OleDbSource Name="Source" ConnectionName="<#= connectionName #>">
          <DirectInput>SELECT * FROM [<#= table.Schema.Name #>].[<#= table.Name #>]</DirectInput>
        </OleDbSource>
      </Transformations>
    </Dataflow>
  </Tasks>
</Package>

Use CallBimlScript when: You need to pass parameters and reuse logic with different inputs.

Deep Dive: Utility Methods

For comprehensive coverage of code reuse including CallBimlScriptWithOutput, helper classes, and extension methods, see Biml Utility Methods.

---

Platform Considerations

BimlScript with BimlExpress/BimlStudio generates SSIS packages directly. For modern cloud platforms, BimlFlex extends the same metadata-driven approach:

Platform	BimlFlex Capability
Azure Data Factory	JSON pipelines, datasets, linked services
Databricks	Notebooks, Delta Lake DDL, workflows
Snowflake	Stored procedures, stages, tasks
Microsoft Fabric	Lakehouse tables, pipelines, notebooks

The BimlScript concepts you learn here (loops, conditionals, metadata-driven generation) apply across all platforms.

See BimlFlex Documentation for platform-specific implementation details.

---

Common Mistakes and Fixes

Mistake 1: Missing Tier Directive

Symptom: "Object reference not set" or "Cannot find connection"

Fix: Add tier directive to control compilation order:

<#@ template tier="20" #>

Mistake 2: Unclosed Control Nugget

Symptom: Malformed XML errors

Problem:

<# foreach (var t in tables) { #>
<Package Name="<#= t.Name #>" />
<!-- Missing closing brace! -->

Fix:

<# foreach (var t in tables) { #>
<Package Name="<#= t.Name #>" />
<# } #>

Mistake 3: Using Text Nugget for Logic

Symptom: Compilation errors

Problem:

<#= if (condition) { #>  <!-- Wrong! -->

Fix: Use control nugget for logic:

<# if (condition) { #>

Mistake 4: Special Characters in Output

Symptom: Invalid XML errors

Problem: Text nuggets output <, >, or & characters

Fix: Escape special characters:

<#= System.Security.SecurityElement.Escape(value) #>

Mistake 5: Empty Loop Results

Symptom: No packages generated

Debug: Check your collection has items:

<#
System.Diagnostics.Debug.WriteLine($"Table count: {RootNode.Tables.Count}");
foreach (var table in RootNode.Tables) {
#>

---

Next Steps

Now that you understand BimlScript fundamentals:

1. Practice: Build Automated Staging Layer - Complete tutorial
2. Patterns: Common Patterns - Ready-to-use staging, incremental, error handling patterns
3. SSIS Deep Dive: Biml for SSIS - Control flows, data flows, event handlers
4. Troubleshooting: Troubleshooting Guide - Debug compilation errors
5. Enterprise: BimlFlex - UI-driven metadata management for production deployments

---

Quick Reference

Code Nugget Types

Nugget	Syntax	Purpose
Control	<# code #>	Execute logic (loops, conditions)
Text	<#= expr #>	Output value as text
Class	<#+ code #>	Define reusable methods
Comment	<#* text *#>	Compiler-ignored comments
Directive	<#@ dir #>	Compiler instructions

Common Directives

Directive	Purpose	Example
template	Set tier, language	<#@ template tier="20" #>
import	Add namespace	<#@ import namespace="System.Linq" #>
include	Insert file contents	<#@ include file="Fragment.biml" #>
property	Define CallBimlScript parameter	<#@ property name="table" type="AstTableNode" #>
code	Reference .cs/.vb file	<#@ code file="Helpers.cs" #>
assembly	Reference DLL	<#@ assembly name="MyLib.dll" #>