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
foreachloop 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:
- Defines connections
- Imports metadata from a source database
- 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
- In BimlExpress, add all three
.bimlfiles to the Live set of the Live Biml Files view - Choose Build Biml on the title bar of the Live Biml Files view
- BimlScript compiles files in tier order (10 → 20 → 30)
- Generated packages appear in the SSIS Target Folder,
output/ssisby default
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.
:::tip 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:
- Practice: Build Automated Staging Layer - Complete tutorial
- Patterns: Common Patterns - Ready-to-use staging, incremental, error handling patterns
- SSIS Deep Dive: Biml for SSIS - Control flows, data flows, event handlers
- Troubleshooting: Troubleshooting Guide - Debug compilation errors
- 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" #> |