Loading lesson map...

Using .NET Control Structures to Fully Automate BimlScript

Transform a single BimlScript template into hundreds of pipelines automatically. This lesson teaches you to use loops, conditionals, and live metadata queries to eliminate manual package creation entirely.

What You'll Learn

Use foreach loops to iterate over table collections
Apply if/else conditionals for platform-specific logic
Query live database schemas with ImportDB() and GetDatabaseSchema()
Access Biml objects through RootNode API
Organize multi-file solutions with tiers
Generate pipelines for SSIS, ADF, Databricks, Snowflake, and Fabric

Prerequisites: Using .NET Variables (understand text/control nuggets)

Why Full Automation Matters

Compare the effort required to create 100 staging packages:

Approach	Time per Package	Total Time	Maintenance Cost
Manual SSDT/ADF UI	15 minutes	25 hours	Edit each package
Variables (previous lesson)	15 seconds	25 minutes	Edit and rebuild each
Control structures	0 seconds	Single build	Rebuild all instantly

Business Benefits

Consistency: Every package follows the same pattern with no human variation
Speed: Add 50 tables to scope? Rebuild takes seconds
Maintainability: Change the pattern once, regenerate everything
Auditability: Template is version-controlled; changes are traceable

Creating Table Collections

Step 1: Define a Static Collection

Start with a hardcoded list to understand the mechanics:

<#
var tableNames = new[] {
  "DimAccount",
  "DimCurrency",
  "DimCustomer",
  "DimDate",
  "DimProduct"
};
#>

This creates a C# string array. The next step iterates over it.

Step 2: Loop with Foreach

Wrap your package definition in a foreach loop:

<#
var tableNames = new[] {
  "DimAccount",
  "DimCurrency",
  "DimCustomer",
  "DimDate",
  "DimProduct"
};
#>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="Source" ConnectionString="Provider=SQLNCLI11;Server=SOURCE_SERVER;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;" />
    <OleDbConnection Name="Target" ConnectionString="Provider=SQLNCLI11;Server=localhost;Initial Catalog=Staging;Integrated Security=SSPI;" />
  </Connections>
  <Packages>
    <# foreach (var tableName in tableNames) { #>
    <Package Name="Load_<#=tableName#>" ConstraintMode="Linear">
      <Tasks>
        <Dataflow Name="DFT_Load">
          <Transformations>
            <OleDbSource Name="Source" ConnectionName="Source">
              <DirectInput>SELECT * FROM [dbo].[<#=tableName#>]</DirectInput>
            </OleDbSource>
            <OleDbDestination Name="Destination" ConnectionName="Target">
              <ExternalTableOutput Table="[stg].[<#=tableName#>]" />
            </OleDbDestination>
          </Transformations>
        </Dataflow>
      </Tasks>
    </Package>
    <# } #>
  </Packages>
</Biml>

Output: Five packages named Load_DimAccount.dtsx, Load_DimCurrency.dtsx, etc.

Key Syntax Points

Element	Purpose
`<# foreach (...) { #>`	Opens loop (control nugget)
`<#=tableName#>`	Outputs current value (text nugget)
`<# } #>`	Closes loop (control nugget)

The opening brace { must be inside the control nugget. The closing brace } needs its own control nugget.

Querying Live Database Metadata

Hardcoded lists require manual updates when tables change. Query the database directly instead.

Using ImportDB (Simple)

ImportDB() connects to a database and retrieves all schema information:

<#
var sourceConn = RootNode.DbConnections["Source"];
var importResult = sourceConn.ImportDB();
#>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <# foreach (var table in importResult.TableNodes) { #>
    <Package Name="Load_<#=table.Schema.Name#>_<#=table.Name#>" ConstraintMode="Linear">
      <Tasks>
        <Dataflow Name="DFT_Load">
          <Transformations>
            <OleDbSource Name="Source" ConnectionName="Source">
              <DirectInput>SELECT * FROM [<#=table.Schema.Name#>].[<#=table.Name#>]</DirectInput>
            </OleDbSource>
            <OleDbDestination Name="Destination" ConnectionName="Target">
              <ExternalTableOutput Table="[stg].[<#=table.Name#>]" />
            </OleDbDestination>
          </Transformations>
        </Dataflow>
      </Tasks>
    </Package>
    <# } #>
  </Packages>
</Biml>

The importResult.TableNodes collection contains full table metadata including columns, data types, and schema information.

Using GetDatabaseSchema (Advanced)

For more control, use GetDatabaseSchema() with filters:

<#@ import namespace="Varigence.Biml.CoreLowerer.SchemaManagement" #>
<#
var sourceConn = RootNode.OleDbConnections["Source"];
var includedSchemas = new List<string> { "Sales", "Person", "Production" };

var tables = sourceConn.GetDatabaseSchema(
  includedSchemas,                          // Include only these schemas
  null,                                     // No table name filter
  ImportOptions.ExcludeForeignKey |         // Skip FK metadata
  ImportOptions.ExcludeViews |              // Skip views
  ImportOptions.ExcludeColumnDefault        // Skip default constraints
).TableNodes;
#>

ImportOptions Reference

Option	Effect
`ExcludeForeignKey`	Faster import, no FK metadata
`ExcludeViews`	Tables only, skip views
`ExcludeColumnDefault`	Skip default constraint metadata
`ExcludeIdentity`	Skip identity column metadata
`ExcludeIndex`	Skip index metadata

Combining options with | (bitwise OR) applies multiple filters.

Accessing Biml Objects with RootNode

RootNode provides access to all objects defined across your Biml files. This enables one file to reference objects defined in another.

Common Collections

Property	Returns	Example
`RootNode.Connections`	All connections	`RootNode.Connections["Source"]`
`RootNode.DbConnections`	Database connections only	`RootNode.DbConnections["Source"]`
`RootNode.OleDbConnections`	OLE DB connections	`RootNode.OleDbConnections["Source"]`
`RootNode.Databases`	Database definitions	`RootNode.Databases["Staging"]`
`RootNode.Schemas`	Schema definitions	`RootNode.Schemas["Staging.dbo"]`
`RootNode.Tables`	Table definitions	`RootNode.Tables["Customer"]`
`RootNode.Packages`	Package definitions	`RootNode.Packages["Load_Customer"]`

Filtering with LINQ

Apply standard LINQ methods to filter and sort collections:

<#
// Tables in Sales schema only
var salesTables = RootNode.Tables.Where(t => t.Schema.Name == "Sales");

// Tables with specific annotation
var fullLoadTables = RootNode.Tables.Where(t => t.GetTag("LoadType") == "Full");

// First 10 tables alphabetically
var firstTen = RootNode.Tables.OrderBy(t => t.Name).Take(10);

// Tables containing "Dim" in name
var dimTables = RootNode.Tables.Where(t => t.Name.Contains("Dim"));

// Count tables per schema
var schemaGroups = RootNode.Tables.GroupBy(t => t.Schema.Name);
#>

Accessing Table Properties

Each table object exposes rich metadata:

<# foreach (var table in RootNode.Tables) { #>
<!--
  Table: <#=table.Name#>
  Schema: <#=table.Schema.Name#>
  Qualified: <#=table.SchemaQualifiedName#>
  Column Count: <#=table.Columns.Count#>
-->
<# } #>

Applying Conditional Logic

Use if/else statements to customize output based on metadata.

Basic Conditional

Add optional steps based on table annotations:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <# foreach (var table in RootNode.Tables) { #>
    <Package Name="Load_<#=table.Name#>" ConstraintMode="Linear">
      <Tasks>
        <# if (table.GetTag("TruncateFirst") == "true") { #>
        <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 [<#=table.Schema.Name#>].[<#=table.Name#>]</DirectInput>
            </OleDbSource>
            <OleDbDestination Name="Destination" ConnectionName="Target">
              <ExternalTableOutput Table="[stg].[<#=table.Name#>]" />
            </OleDbDestination>
          </Transformations>
        </Dataflow>
      </Tasks>
    </Package>
    <# } #>
  </Packages>
</Biml>

Load Type Logic

Handle different load patterns based on metadata:

<#
// Inside foreach loop
var loadType = table.GetTag("LoadType") ?? "Full";
string preSql = "";
string whereSql = "";

if (loadType == "Full") {
  preSql = $"TRUNCATE TABLE [stg].[{table.Name}]";
  whereSql = "";
} else if (loadType == "Incremental") {
  preSql = "";
  whereSql = "WHERE ModifiedDate > @LastLoadDate";
} else if (loadType == "Append") {
  preSql = "";
  whereSql = "";
}
#>

Conditional Column Processing

Apply transformations based on column data types:

<# foreach (var table in RootNode.Tables) { #>
<Dataflow Name="Load_<#=table.Name#>">
  <Transformations>
    <OleDbSource Name="Source" ConnectionName="Source">
      <DirectInput>SELECT * FROM [<#=table.Schema.Name#>].[<#=table.Name#>]</DirectInput>
    </OleDbSource>
    <#
    var ansiColumns = table.Columns.Where(c => c.DataType == System.Data.DbType.AnsiString);
    if (ansiColumns.Any()) {
    #>
    <DataConversion Name="ConvertToUnicode">
      <Columns>
        <# foreach (var col in ansiColumns) { #>
        <Column SourceColumn="<#=col.Name#>" TargetColumn="<#=col.Name#>_Unicode" DataType="String" Length="<#=col.Length#>" />
        <# } #>
      </Columns>
    </DataConversion>
    <# } #>
    <OleDbDestination Name="Destination" ConnectionName="Target">
      <ExternalTableOutput Table="[stg].[<#=table.Name#>]" />
    </OleDbDestination>
  </Transformations>
</Dataflow>
<# } #>

Organizing Multi-File Solutions

Complex solutions require multiple Biml files with controlled compilation order.

Tier System

Use the tier directive to control compilation order:

<#@ template tier="10" #>  <!-- Compiles first: connections, databases -->
<#@ template tier="20" #>  <!-- Compiles second: tables referencing connections -->
<#@ template tier="30" #>  <!-- Compiles third: packages referencing tables -->

Lower numbers compile first. Objects must exist before they can be referenced.

File Naming Convention

Use numbered prefixes to indicate build steps:

1-2-Environment.biml        # Step 1 and 2: connections, databases, schemas
1-2-CreateTables.biml       # Step 1 and 2: table definitions
1-x-DeployTables.biml       # Step 1 only: DDL deployment package
x-2-CreateLoadPackages.biml # Step 2 only: load packages

Build Step 1: Select files starting with 1-
Build Step 2: Select files starting with x-2 or containing -2-

Example: Complete Staging Solution

1-2-Environment.biml (tier 10)

<#@ template tier="10" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="Source" ConnectionString="Provider=SQLNCLI11;Server=SOURCE_SERVER;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;" />
    <OleDbConnection Name="Target" ConnectionString="Provider=SQLNCLI11;Server=localhost;Initial Catalog=Staging;Integrated Security=SSPI;" />
  </Connections>
  <Databases>
    <Database Name="Staging" ConnectionName="Target" />
  </Databases>
  <Schemas>
    <Schema Name="stg" DatabaseName="Staging" />
  </Schemas>
</Biml>

1-2-CreateTables.biml (tier 20)

<#@ template tier="20" #>
<#@ import namespace="Varigence.Biml.CoreLowerer.SchemaManagement" #>
<#
var source = RootNode.OleDbConnections["Source"];
var tables = source.GetDatabaseSchema(
  null, null,
  ImportOptions.ExcludeForeignKey | ImportOptions.ExcludeViews
).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 col in table.Columns) { #>
        <#=col.GetBiml()#>
        <# } #>
        <Column Name="LoadDateTime" DataType="DateTime2" />
      </Columns>
      <Annotations>
        <Annotation Tag="SourceTable"><#=table.SchemaQualifiedName#></Annotation>
      </Annotations>
    </Table>
    <# } #>
  </Tables>
</Biml>

1-x-DeployTables.biml (tier 30)

<#@ template tier="30" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <Package Name="DeployTables" ConstraintMode="Parallel">
      <Tasks>
        <# foreach (var table in RootNode.Tables) { #>
        <ExecuteSQL Name="Create_<#=table.Name#>" ConnectionName="Target">
          <DirectInput><#=table.GetDropAndCreateDdl()#></DirectInput>
        </ExecuteSQL>
        <# } #>
      </Tasks>
    </Package>
  </Packages>
</Biml>

x-2-CreateLoadPackages.biml (tier 30)

<#@ template tier="30" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <# foreach (var table in RootNode.Tables) { #>
    <Package Name="Load_<#=table.Schema.Name#>_<#=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="Src" ConnectionName="Source">
              <DirectInput>SELECT * FROM <#=table.GetTag("SourceTable")#></DirectInput>
            </OleDbSource>
            <DerivedColumns Name="AddAudit">
              <Columns>
                <Column Name="LoadDateTime" DataType="DateTime2" Scale="7">(DT_DBTIMESTAMP2,7)GETDATE()</Column>
              </Columns>
            </DerivedColumns>
            <OleDbDestination Name="Dst" ConnectionName="Target">
              <ExternalTableOutput Table="[<#=table.Schema.Name#>].[<#=table.Name#>]" />
            </OleDbDestination>
          </Transformations>
        </Dataflow>
      </Tasks>
    </Package>
    <# } #>

    <!-- Master workflow package -->
    <Package Name="Workflow_LoadAll" ConstraintMode="Parallel">
      <Tasks>
        <# foreach (var table in RootNode.Tables) { #>
        <ExecutePackage Name="Run_<#=table.Name#>">
          <ExternalProjectPackage Package="Load_<#=table.Schema.Name#>_<#=table.Name#>.dtsx" />
        </ExecutePackage>
        <# } #>
      </Tasks>
    </Package>
  </Packages>
</Biml>

Platform-Specific Examples

The same automation principles apply across all Biml-supported platforms.

Azure Data Factory

Generate Copy Activities for each table:

<# foreach (var table in RootNode.Tables) { #>
<Pipeline Name="Copy_<#=table.Name#>">
  <Activities>
    <Copy Name="CopyData" EnableStaging="false">
      <Source xsi:type="AzureSqlSource">
        <SqlReaderQuery>SELECT * FROM [<#=table.Schema.Name#>].[<#=table.Name#>]</SqlReaderQuery>
      </Source>
      <Sink xsi:type="AzureSqlSink">
        <TableName>[stg].[<#=table.Name#>]</TableName>
        <PreCopyScript>TRUNCATE TABLE [stg].[<#=table.Name#>]</PreCopyScript>
      </Sink>
    </Copy>
  </Activities>
</Pipeline>
<# } #>

Databricks

Generate MERGE statements for each table:

<# foreach (var table in RootNode.Tables) { #>
-- Load <#=table.Name#>
MERGE INTO staging.<#=table.Name#> AS target
USING source.<#=table.Name#> AS source
ON target.id = source.id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

<# } #>

Snowflake

Generate COPY INTO statements:

<# foreach (var table in RootNode.Tables) { #>
-- Load <#=table.Name#> from stage
COPY INTO staging.<#=table.Name#>
FROM @source_stage/<#=table.Name#>/
FILE_FORMAT = (TYPE = PARQUET)
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

<# } #>

Microsoft Fabric

Generate Lakehouse table definitions:

<# foreach (var table in RootNode.Tables) { #>
-- Create staging table for <#=table.Name#>
CREATE TABLE IF NOT EXISTS lakehouse.staging.<#=table.Name#> (
  <# foreach (var col in table.Columns) { #>
  <#=col.Name#> <#=GetSparkDataType(col.DataType)#>,
  <# } #>
  LoadDateTime TIMESTAMP
)
USING DELTA;

<# } #>

For comprehensive platform-specific guidance, see BimlFlex Documentation.

CI/CD Integration Benefits

Automated Biml generation integrates naturally with CI/CD pipelines.

Workflow

Developer commits BimlScript template changes
CI pipeline triggers Biml build
Build generates all packages/pipelines
Automated tests validate generated artifacts
CD pipeline deploys to target environment

Key Advantages

Traditional Approach	Biml Automation
Commit 100 package files	Commit 1 template file
Review 100 package diffs	Review 1 template diff
Merge conflicts in packages	Merge conflicts in template only
Manual deployment of changed packages	Regenerate and deploy all

Command-Line Build

# BimlStudio command-line build
bimlstudio.exe build MyProject.mst /output:./output

# Integrate with Azure DevOps, GitHub Actions, Jenkins

Common Issues and Fixes

Loop Not Generating Output

Symptom: Package element appears once or not at all

Checks:

Verify collection isn't empty: <# WriteLine($"Table count: {tables.Count()}"); #>
Ensure closing brace has its own nugget: <# } #>
Check tier order: referenced objects must compile first

"Object reference not set" Error

Symptom: Null reference exception during build

Cause: Accessing property on null object

Fix:

<#
// Wrong - crashes if GetTag returns null
var loadType = table.GetTag("LoadType").ToUpper();

// Right - use null-conditional operator
var loadType = table.GetTag("LoadType")?.ToUpper() ?? "FULL";
#>

Connection Not Found

Symptom: RootNode.DbConnections["Source"] returns null

Cause: Connection file has higher tier than current file

Fix: Ensure connection file has lower tier number:

<!-- Environment.biml -->
<#@ template tier="10" #>

<!-- LoadPackages.biml -->
<#@ template tier="20" #>

ImportDB Returns No Tables

Symptom: importResult.TableNodes is empty

Checks:

Connection string is valid and server accessible
User has permission to read metadata
Database contains tables (not just views)
Schema filter isn't excluding all tables

Multi-Select Build Issues (BimlExpress)

Symptom: Objects not found when building

Cause: Not all required files selected

Fix: In BimlExpress, Ctrl+Click to multi-select all dependent Biml files before right-clicking to build.

Quick Reference

Loop Syntax

Pattern	Syntax
Open loop	`<# foreach (var item in collection) { #>`
Output value	`<#=item.Property#>`
Close loop	`<# } #>`
Conditional	`<# if (condition) { #> ... <# } #>`
Else	`<# } else { #>`

Metadata Methods

Method	Purpose
`ImportDB()`	Quick schema import
`GetDatabaseSchema()`	Filtered schema import
`GetBiml()`	Convert object to Biml XML
`GetDropAndCreateDdl()`	Generate DDL script
`GetColumnList()`	Comma-separated column names
`GetTag("name")`	Read annotation value

Common LINQ Operations

Operation	Example
Filter	`.Where(t => t.Schema.Name == "Sales")`
Sort	`.OrderBy(t => t.Name)`
Limit	`.Take(10)`
Check exists	`.Any()`
Count	`.Count()`

Next Steps

Common Patterns - Reusable patterns for staging, incremental loads, error handling
Build Staging Layer - Complete end-to-end example
Automation Philosophy - Design principles for maintainable solutions
Troubleshooting Guide - Debug compilation errors

What You'll Learn​

Why Full Automation Matters​

Business Benefits​

Creating Table Collections​

Step 1: Define a Static Collection​

Step 2: Loop with Foreach​

Key Syntax Points​

Querying Live Database Metadata​

Using ImportDB (Simple)​

Using GetDatabaseSchema (Advanced)​

ImportOptions Reference​

Accessing Biml Objects with RootNode​

Common Collections​

Filtering with LINQ​

Accessing Table Properties​

Applying Conditional Logic​

Basic Conditional​

Load Type Logic​

Conditional Column Processing​

Organizing Multi-File Solutions​

Tier System​

File Naming Convention​

Example: Complete Staging Solution​

Platform-Specific Examples​

Azure Data Factory​

Databricks​

Snowflake​

Microsoft Fabric​

CI/CD Integration Benefits​

Workflow​

Key Advantages​

Command-Line Build​

Common Issues and Fixes​

Loop Not Generating Output​

"Object reference not set" Error​

Connection Not Found​

ImportDB Returns No Tables​

Multi-Select Build Issues (BimlExpress)​

Quick Reference​

Loop Syntax​

Metadata Methods​

Common LINQ Operations​

Next Steps​