Hadi Fadlallah
Biml code editor and preview in Visual studio

Extending Biml with C# scripts

March 16, 2020 by

In our previously published articles in the Biml series, we have explained what Biml is, and how to use this language to generate SQL Server Integration Services (SSIS) packages. In this article, we will explain BimlScript, which is an extension of this markup language with VB or C# scripts.

When working as a data engineer or business intelligence developer, sometimes you may need to build a number of packages which require a lot of repetitive work (i.e., if we need to transfer multiple tables with differents schema to another database by applying the same transformations over each table), this task is time-consuming using SSIS or even using static Biml code. For this reason, BimlScript was developed.

What is BimlScript?

BimScript is an automation tool to generate, control, and manipulate Biml scripts. It can read database metadata, loop over database objects, and replace static values with expressions.

In BimlScript, you have to use one of the following code blocks to write a script or comment:

Code block

Type

Description

<# #>

Control

Used to implement a control logic such as reading database metadata

<#= #>

Text

Used to return a string value

<#@ #>

Directive

Used to add compiler instructions

<#+ #>

Class

Used to create a C#/VB class

<#* *#>

Comment

Used to add a comment

Automating building packages using BimlScript

Assuming we need to migrate all tables that belong to the Person schema within the AdventureWorks database. For each table, we need to add a derived column that contains the current date and time (using SSIS GETDATE() function).

To do that, using BimlScript, first add a new Biml Script file to your solution. Next, you should do the following steps:

  1. Specifying the script language (C# or VB) and import needed assemblies
  2. Retrieve tables metadata from AdventureWorks database
  3. Loop over tables and generate a package for each table
  4. Define connection managers within the script
  5. Add and configure a data flow task within each package

In this section, we will explain how to do each step. Then we will generate the packages after combining all parts.

Configuring script

To extend Biml with C# or VB script, you should first add a directive block to specify the language you want to use. Then you have to import all the assemblies you need to use within the script. Note that this part of code must be added outside the main element <Biml> as following:

Retrieve tables metadata from AdventureWorks database

To retrieve table metadata from a database, you should add two control blocks before the main <biml> element in the script; the first block is to establish a connection and the other is to retrieve the table’s metadata from it. You should use a similar code:

You can read more about the Import Metadata in using GetDatabaseSchema from the amazing Catherine Wilhelmsen website.

Define connection managers

Since all packages will use the same connection managers, we will define them at the project level. To do that, we will define them outside of <packages> element as mentioned below:

Loop over tables

As described in the previous articles in this series, all packages should be defined within <packages> element, and since we are looking to generate multiple packages, we should implement the loop logic within this element. We need a loop over tables retrieved, add a package for each table where the table name is “Extract_” + table name. We should use the following code to achieve that:

Note that we have used a Text block to evaluate the package name as expression:

Add and configure Data Flow Task

For each package, we should add a Data Flow task that contains an OLE DB Source, one Derived Column transformation, and an OLE DB Destination. In this example, we will extract data from the AdventureWorks2017 database and load it into tempdb. We need to add the following biml code within <package> element:

Generating package

The whole BimlScript code should look like:

You can check the Biml generated from this script in the preview window available in the Visual studio editor:

Biml code editor and preview in Visual studio

Figure 1 – Script editor

The following code is generated from the script above:

To generate packages, right-click on the Biml script file in the solution explorer, and click on “Generate SSIS packages”:

Generating SSIS packages from Biml script

Figure 2 – Generating SSIS packages from the script

After Biml expansion is completed, you can see that multiple packages are added to the solution explorer:

Generated SSIS packages

Figure 3 – Generated SSIS packages

If we open one of these packages, we can see that it contains a data flow task and two connection managers:

generated package control flow

Figure 4 – Generated package control flow

Also, we can see that all components are created successfully within the data flow task:

generated package data flow task

Figure 5 – Generated package data flow task

Conclusion

BimlScript is a very powerful extension for Biml. It prevents doing a lot of repetitive work and decreases development time. In this article, we have explained what BimlScript is and why it should be used. Also, we showed how to generate multiple packages from a simple BimlScript code.

Table of contents

Getting started with Biml
Converting SSIS packages to Biml scripts
Using Biml scripts to generate SSIS packages
Extending Biml with C# scripts
Using external C# script files within Biml
Biml alternatives: Building SSIS packages programmatically using ManagedDTS
Biml alternatives: Building SSIS packages programmatically using EzAPI
Building databases using BIML
Building SSAS OLAP cubes using Biml
Building SSAS tabular models using Biml
Hadi Fadlallah
Biml, ETL, Integration Services (SSIS)

About Hadi Fadlallah

Hadi is an SQL Server professional with more than 10 years of experience. His main expertise is in data integration. He's one of the top ETL and SQL Server Integration Services contributors at Stackoverflow.com . Also, he published several article series about Biml, SSIS features, Search engines, Hadoop, and many other technologies. Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a MongoDB, Neo4j, and ArangoDB certified professional. On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality assessment techniques. Hadi really enjoys learning new things everyday and sharing his knowledge. You can reach him on his personal website. View all posts by Hadi Fadlallah

168 Views