Using BimlScript to Increase SQL Server Integration Services (SSIS) Productivity

By: Nat Sundar   |   Updated: 2016-10-19   |   Comments (1)   |   Related: More > Integration Services Development

Problem

What is BimlScript and how can I use it to increase SQL Server Integration Services development productivity?

Solution

BimlScript is made of C# code snippets, which helps to dynamically generate BIML (Business Intelligence Markup Language).

The BIML script is the programing construct of BIML. If you know the basics of C#, then you may find it very easy to use and unleash the power of BIML.

All BimlScript tags begin with "<#" and end with "#>" delimiters. BimlScript exist within the BIML file. Usually the BIML script contains directives. These directives give instructions to the BIML engine to process the BIML script. The directives begin with the tag "<#@" and end with "#>".

Specify BIML Language

A sample directive can be found below.

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

This directive instructs the BIML engine to use C# to process the BIML script.

Directive to import .Net library

If you are familiar with C# or VB.Net then you know about the Import functionality. The equivalent of the Import functionality in BIML can be represented below.

<#@ import namespace="Varigence.Languages.Biml.Connection" #>

Embedding in BIML File

The start tag "<#=" evaluates the .NET code and converts it to a string. The final string will be embedded in the BIML file.

BIML Script example - truncate table

Let’s start with very simple example to understand BIML script.

In this example, we are building a staging package to truncate a table. So instead of hard coding the name of the table, we would like to use a variable. A variable "strTableName" has been defined and a value has been assigned.

The below code snippet will assign the value to the variable strTableName.

<#
 Var strTableName="stg.Customer";
#>

Now the variable has been referenced as below in a SQL query.

Truncate table <#=strTableName #>

The tag "<#=" helps the BIML run-time engine to evaluate the variable and replace the value in the BIML file.

After the evaluation, the SQL Query will become:

Truncate table Stg.Customer

The below script will help us to create a package with the execute SQL task to truncate the staging table.

<# 
 var strTableName="Stg.Customer"; 
#>
<Biml xmlns="http://schemas.varigence.com/biml.xsd" >
 <Connections>
  <Connection Name="Dev_Server" ConnectionString="Data Source=(local);Initial Catalog=Staging_DB;Provider=SQLNCLI11.1;Integrated Security=SSPI;" />
 </Connections>
<Packages>
 <Package Name="LoadStaging" ConstraintMode="Linear">
  <Tasks>
   <ExecuteSQL Name="Run SQL" ConnectionName="Dev_Server">
    <DirectInput>Truncate table <#=strTableName #>/DirectInput>
   </ExecuteSQL>
  </Tasks>
 </Package>
</Packages>
</Biml>

Once the BIML has been validated for errors, the SSIS package can be generated as below.

BIML Script example - truncate multiple tables

Now let's assume that we have many staging tables and we want to truncate all the staging tables. The classic approach is to prepare one SQL task for each table. Now instead of creating a lengthy BIML to create multiple SQL tasks, we can achieve the same with a few lines of BIML script.

Let's define a string array to hold the staging tables as below.

String[] arrStagingTables = new string[] {"Customer","product","SalesTransaction","Supplier"};

Now with the help of the foreach loop, we can iterate through the array and get the name of the individual tables. This can be achieved by using the BIMLScript as below.

<#
 foreach (var stgTable in arrStagingTable)
  {
#>

If we place the Execute SQL task within the foreach loop, this will create multiple SQL Tasks at run time. The complete script has been provided below.

<# 
 string[] arrStagingTables = new string[] {"Customer","Product","SalesTransactionn","Supplier"}; 
#>
<Biml xmlns="http://schemas.varigence.com/biml.xsd" >
 <Connections>
  <Connection Name="Dev_Server" ConnectionString="Data Source=(local);Initial Catalog=Staging_DB;Provider=SQLNCLI11.1;Integrated Security=SSPI;" />
 </Connections>
<Packages>
 <Package Name="LoadStaging" ConstraintMode="Linear">
  <Tasks> 
  <# 
   foreach (var stgTable in arrStagingTables) 
    { 
  #> 
  <ExecuteSQL Name="Truncate <#=stgTable #>" ConnectionName="Dev_Server"> 
   <DirectInput>Truncate table <#=stgTable #></DirectInput> 
  </ExecuteSQL> 
    <# } #> 
  </Tasks> 
 </Package> 
</Packages> 
</Biml> 

After we run Check Biml for errors and Generate SSIS packages, the SSIS package will look like the below.

This solution has simplified our approach to conventional development practices.

BIML Script example - generate multiple staging packages

As a good practice, you may want to create separate packages for each individual staging table. This can be easily achieved by introducing a few changes to the BIMLScript.

If we keep the tag "Package" and its child tags within the foreach loop, then we will be able to generate multiple packages with a simple BIML Script.

The below script will generate multiple packages.

<# 
 string[] arrStagingTables = new string[] {"Customer","Product","SalesTransactionn","Supplier"}; 
#>
<Biml xmlns="http://schemas.varigence.com/biml.xsd" >
 <Connections>
  <Connection Name="Dev_Server" ConnectionString="Data Source=(local);Initial Catalog=Staging_DB;Provider=SQLNCLI11.1;Integrated Security=SSPI;" />
 </Connections>
<Packages> 
 <# 
  foreach (var stgTable in arrStagingTables) 
   { 
 #> 
 <Package Name="LoadStaging<#=stgTable#>" ConstraintMode="Linear" > 
 <Tasks> 
  <ExecuteSQL Name="Truncate <#=stgTable #>" ConnectionName="Dev_Server"> 
   <DirectInput>Truncate table <#=stgTable #></DirectInput> 
  </ExecuteSQL> 
 </Tasks> 
</Package> 
 <# 
  } 
 #> 
</Packages> 
</Biml> 

After we run Check Biml for errors and Generate SSIS packages, multiple SSIS packages will be created.

Summary

Based on the above examples it is evident that BimlScript will increase the productivity for SSIS package development. It is relatively easy to learn BIML and BimlScript. It requires limited knowledge about C# or VB.net to become a master with BimlScript.

Next Steps

• Stay tuned to learn more about using BimlScript for Meta Data Driven Development in my next tip.
• Learn BIML basics in this tip.
• More BimlScript code snippets can be found here.
• You may need this helper to practice BimlScript.

About the author

Nat Sundar is working as an independent SQL BI consultant in the UK with a Bachelors Degree in Engineering.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips