Using .NET Variables to Partially Automate BimlScript

Variables are the first step toward automation. Instead of copying and editing Biml code for each table, you define the varying parts as variables and change only those values. This simple technique dramatically reduces the time to create repetitive packages.

What You'll Learn

Declare C# variables in control nuggets
Insert variable values using text nuggets
Use multiple variables for schema, table, and connection names
Work with different variable types (strings, integers, booleans)
Apply variable naming best practices

Prerequisites: Introduction to BimlScript (understand code nugget types)

Why Use Variables?

Before exploring the technique, consider the time savings:

Approach	Time per Package	30 Packages Total
Manual SSDT	15 minutes	7.5 hours
Plain Biml (copy/paste)	5 minutes	2.5 hours
BimlScript variables	15 seconds	7.5 minutes

Variables don't eliminate repetition entirely—you still change the variable value and rebuild for each package. But that 15-second change-and-build cycle is dramatically faster than editing package properties manually.

The next lesson on control structures removes even this manual step, generating all packages in one build.

The Scenario

You need to copy data from 30 tables on a source server to matching tables on a target server. The servers aren't linked, so SSIS is a natural choice.

The challenge: Creating 30 nearly identical packages manually is tedious and error-prone. Each package follows the same pattern—only the table name changes.

The solution: Create a template with variables for the changing parts. Change the variable, rebuild, and get a customized package in seconds.

Step 1: Create a Working Template

Start with actual working Biml code. Don't add automation until you have a single package that builds and runs correctly.

<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=AdventureWorksDW2012;Integrated Security=SSPI;" />
    </Connections>
    <Packages>
        <Package Name="Copy DimAccount" ConstraintMode="Parallel">
            <Tasks>
                <Dataflow Name="DFT_CopyData">
                    <Transformations>
                        <OleDbSource Name="Retrieve Rows" ConnectionName="Source">
                            <DirectInput>SELECT * FROM dbo.DimAccount</DirectInput>
                        </OleDbSource>
                        <OleDbDestination Name="Load Rows" ConnectionName="Target">
                            <ExternalTableOutput Table="dbo.DimAccount" />
                        </OleDbDestination>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Build this and verify you get a valid SSIS package. Once it works, you're ready to add variables.

Step 2: Identify Values to Replace

Look at your template and find the values that change for each table:

Location	Current Value	What Changes
Package Name attribute	`"Copy DimAccount"`	Table name
SELECT statement	`"dbo.DimAccount"`	Schema and table
ExternalTableOutput Table	`"dbo.DimAccount"`	Schema and table

In this scenario, three places reference the table name. A variable lets you change all three by editing one line.

Step 3: Declare Variables

Add a control nugget at the top of your file to declare variables. Control nuggets use <# ... #> syntax:

Single Variable

<# var tableName = "DimAccount"; #>

This declares a C# variable named tableName with the value "DimAccount". The var keyword lets C# infer the type (string in this case).

Multiple Variables

For more flexibility, use multiple variables:

<#
    var schemaName = "dbo";
    var tableName = "DimAccount";
    var sourceConn = "Source";
    var targetConn = "Target";
#>

Why separate schema and table? Source and target schemas often differ. Your source might use Sales.Customer while your staging area uses stg.Customer.

Variable Naming Best Practices

Practice	Good	Avoid
Use camelCase	`tableName`	`TableName`, `table_name`
Be descriptive	`sourceSchemaName`	`s`, `schema1`
Avoid reserved words	`tableName`	`table`, `string`
Group related variables	Keep together	Scatter throughout file

Step 4: Use Text Nuggets to Insert Values

Text nuggets use <#= ... #> syntax. The equals sign is critical—it tells BimlScript to output the expression's value:

<Package Name="Copy <#=tableName#>">

When BimlScript processes this, it replaces <#=tableName#> with DimAccount, producing:

<Package Name="Copy DimAccount">

Complete Example

Here's the full template with variables:

<#
    // Configuration variables - change these values to generate different packages
    var schemaName = "dbo";
    var tableName = "DimAccount";
#>
<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=AdventureWorksDW2012;Integrated Security=SSPI;" />
    </Connections>
    <Packages>
        <Package Name="Copy_<#=schemaName#>_<#=tableName#>" ConstraintMode="Parallel">
            <Tasks>
                <Dataflow Name="DFT_CopyData">
                    <Transformations>
                        <OleDbSource Name="Retrieve Rows" ConnectionName="Source">
                            <DirectInput>SELECT * FROM [<#=schemaName#>].[<#=tableName#>]</DirectInput>
                        </OleDbSource>
                        <OleDbDestination Name="Load Rows" ConnectionName="Target">
                            <ExternalTableOutput Table="[<#=schemaName#>].[<#=tableName#>]" />
                        </OleDbDestination>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Workflow:

Set tableName = "DimAccount", build → Copy_dbo_DimAccount.dtsx
Set tableName = "DimCurrency", build → Copy_dbo_DimCurrency.dtsx
Repeat for remaining tables

Each iteration takes about 15 seconds instead of 15 minutes.

Beyond Simple Strings

Variables aren't limited to strings. Here are other useful types:

Integer Variables

Control batch sizes, row counts, or numeric configurations:

<#
    var batchSize = 10000;
    var maxErrors = 100;
#>
<OleDbDestination Name="Load Rows" ConnectionName="Target" MaximumInsertCommitSize="<#=batchSize#>">

Boolean Variables for Conditionals

Use booleans to enable or disable features:

<#
    var truncateFirst = true;
    var tableName = "DimAccount";
#>
<Tasks>
    <# if (truncateFirst) { #>
    <ExecuteSQL Name="Truncate Target" ConnectionName="Target">
        <DirectInput>TRUNCATE TABLE [stg].[<#=tableName#>]</DirectInput>
    </ExecuteSQL>
    <# } #>
    <Dataflow Name="DFT_CopyData">
        <!-- Data flow content -->
    </Dataflow>
</Tasks>

When truncateFirst is true, the package includes a truncate step. When false, it skips directly to the data flow.

DateTime Variables for Dynamic Naming

Add timestamps to package names or log entries:

<#
    var buildDate = DateTime.Now;
    var tableName = "DimAccount";
#>
<Package Name="Copy_<#=tableName#>_<#=buildDate.ToString("yyyyMMdd")#>">

This produces names like Copy_DimAccount_20240115.dtsx.

String Methods

Apply transformations to variable values:

<#
    var tableName = "DimAccount";
#>
<!-- Uppercase: <#=tableName.ToUpper()#> produces "DIMACCOUNT" -->
<!-- Lowercase: <#=tableName.ToLower()#> produces "dimaccount" -->
<!-- Substring: <#=tableName.Substring(3)#> produces "Account" -->

Platform Considerations

While this guide focuses on SSIS package generation, the variable replacement technique applies to all Biml-supported platforms:

Platform	Variable Usage
SSIS	Package names, connection strings, table references
ADF	Pipeline names, dataset references, linked service names
Databricks	Notebook parameters, table paths, schema names
Snowflake	Stage names, warehouse references, schema paths
Microsoft Fabric	Lakehouse paths, notebook parameters

BimlFlex extends these patterns with a metadata-driven UI, eliminating manual variable changes entirely. See BimlFlex Documentation for enterprise-scale automation.

Common Mistakes and Fixes

Wrong:

<Package Name="Copy <#tableName#>">

Right:

<Package Name="Copy <#=tableName#>">

Without the =, BimlScript treats it as a control nugget (code to execute) rather than a text nugget (value to output). The package name becomes literally "Copy " with nothing after it.

2. Variable Used Before Declaration

Wrong:

<Package Name="Copy <#=tableName#>">
<# var tableName = "DimAccount"; #>

Right:

<# var tableName = "DimAccount"; #>
<Package Name="Copy <#=tableName#>">

Variables must be declared before use. BimlScript processes top-to-bottom.

3. Case Sensitivity

Wrong:

<# var TableName = "DimAccount"; #>
<Package Name="Copy <#=tablename#>">

Right:

<# var tableName = "DimAccount"; #>
<Package Name="Copy <#=tableName#>">

C# is case-sensitive. TableName and tablename are different variables.

4. Missing Quotes Around String Values

Wrong:

<# var tableName = DimAccount; #>

Right:

<# var tableName = "DimAccount"; #>

String literals require double quotes in C#.

5. Forgetting to Rebuild

After changing variable values, you must rebuild to generate updated packages. The Biml file is a template—changes don't apply until you run the build process.

Quick Reference

Variable Declaration Syntax

Syntax	Description	Example
`<# var x = "value"; #>`	Declare string variable	`<# var tableName = "Customer"; #>`
`<# var x = 100; #>`	Declare integer variable	`<# var batchSize = 10000; #>`
`<# var x = true; #>`	Declare boolean variable	`<# var truncateFirst = true; #>`

Value Insertion Syntax

Syntax	Description	Example
`<#=variableName#>`	Insert variable value	`<#=tableName#>`
`<#=var.Method()#>`	Call method on variable	`<#=tableName.ToUpper()#>`
`<#=expression#>`	Insert expression result	`<#=batchSize * 2#>`

Common String Methods

Method	Input	Output
`.ToUpper()`	`"Customer"`	`"CUSTOMER"`
`.ToLower()`	`"Customer"`	`"customer"`
`.Length`	`"Customer"`	`8`
`.Replace("a","b")`	`"Data"`	`"Dbtb"`
`.Substring(0,3)`	`"Customer"`	`"Cus"`

Next Steps

Control Structures for Full Automation - Generate all 30 packages in one build using loops
Common Patterns - Reusable patterns for staging, incremental loads, error handling
Automation and Patterns - Metadata-driven development concepts
Troubleshooting Guide - Debug compilation errors

What You'll Learn​

Why Use Variables?​

The Scenario​

Step 1: Create a Working Template​

Step 2: Identify Values to Replace​

Step 3: Declare Variables​

Single Variable​

Multiple Variables​

Variable Naming Best Practices​

Step 4: Use Text Nuggets to Insert Values​

Complete Example​

Beyond Simple Strings​

Integer Variables​

Boolean Variables for Conditionals​

DateTime Variables for Dynamic Naming​

String Methods​

Platform Considerations​

Common Mistakes and Fixes​

1. Missing Equals Sign in Text Nugget​

2. Variable Used Before Declaration​

3. Case Sensitivity​

4. Missing Quotes Around String Values​

5. Forgetting to Rebuild​

Quick Reference​

Variable Declaration Syntax​

Value Insertion Syntax​

Common String Methods​

Next Steps​

What You'll Learn

Why Use Variables?

The Scenario

Step 1: Create a Working Template

Step 2: Identify Values to Replace

Step 3: Declare Variables

Single Variable

Multiple Variables

Variable Naming Best Practices

Step 4: Use Text Nuggets to Insert Values

Complete Example

Beyond Simple Strings

Integer Variables

Boolean Variables for Conditionals

DateTime Variables for Dynamic Naming

String Methods

Platform Considerations

Common Mistakes and Fixes

1. Missing Equals Sign in Text Nugget

2. Variable Used Before Declaration

3. Case Sensitivity

4. Missing Quotes Around String Values

5. Forgetting to Rebuild

Quick Reference

Variable Declaration Syntax

Value Insertion Syntax

Common String Methods

Next Steps