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


By:   |   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.

Check BIML For Errors

Generate SSIS Package

SSIS Execute SQL Task Editor to Truncate the Table

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.

Multiple Staging Table Truncate SSIS Package

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.

Generated Multiple SSIS Packages with BIML

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.


Last Updated: 2016-10-19


get scripts

next tip button



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

View all my tips




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Wednesday, October 19, 2016 - 9:10:50 AM - Koen Verbeeck Back To Top

You don't really needs BIDSHelper to compile your BIML code (BIDSHelper is not always updated as quickly. For example, there's no official release yet for VS 2015). It's better to use the Varigence BIMLExpress add-in for Visual Studio:

https://varigence.com/BimlExpress



download


get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools