Certain ETL projects have some repetitive tasks such as loading multiple flat files with different formats or loading multiple tables from transactional systems to staging tables. This can require creating multiple packages and dataflow tasks for these type of scenarios which can be time-consuming and lack any form of reusability. This is where BIML and BIMLScript really show their value.
BIML or Business Intelligence Markup Language is an easy-to-read XML dialect, which can be used to generate SSIS packages. This is different than the XML dialect used for the SSIS Packages themselves, which can be viewed by pressing F7 while selecting any SSIS Package. The XML dialect used in dtsx files is not designed to be in a human-readable format. On the other hand, BIML has a simple XML document structure that is easy to follow in order to generate SSIS Packages as shown in the following diagram:
BIML by itself can be only used to generate a template for static SSIS packages, which in most cases is not very useful. However, BIMLScript makes it possible to automate the generation of SSIS packages that have the same design pattern by embedding C# or Visual Basic code directly into a BIML file. It is almost always required to have the metadata information of the Source and Target systems in order to effectively build a BIMLScript that can handle creating SSIS packages automatically which is a major point to consider before using BIML.
Used Tools
In order to work with BIML, you can use BIMLStudio, which is a commercial product that offers a complete integrated development environment (IDE) for BIML. However, in this blogpost, we are going to work with BIMLExpress, which is a free Visual Studio add-in to edit BIML code, and it is similar to the BI Developer Extensions (formerly known as BIDSHelper) found in earlier versions of Visual Studio. Also, BIMLOnline is worth mentioning as it is a feature-rich web-based IDE that can be used to create and manage BIML projects online. It has fewer features than BIMLStudio but it is more powerful than BIMLExpress. One of the biggest features of BIMLOnline is the package importer, which allows you to reverse-engineer any SSIS packages to BIML code. This is extremely useful especially when working with third-party SSIS components like ours. However, this feature has recently been added to BIMLExpress 2018 release.
Working with BIMLExpress
After installing the bimlexpress.vsix Visual Studio add-in, you should be able to Add New BIML files by simply right-clicking on the project name as shown in the screenshot below:
You can also choose Convert SSIS Packages to BIML option, which will lunch the Import Packages wizard to reverse-engineer your SSIS packages to BIML code if you are using BIMLExpress 2018.
Once a BimlScript.biml file has been created, it will go under the Miscellaneous folder in the Solution Explorer. When it is opened, the BIML window will be divided into two sections, the upper section will have the BIMLScript code (including any C# or VB .NET code), while the bottom section will have the generated BIML XML code from the BIMLScript, which will be used to create the actual SSIS packages. For example, the following is a simple BIML code to create a single empty package:
However, You can write any C# or VB .NET code between these tags: <# YourCodeHere..#>. The following example is a BIMLScript that will generate five different packages:
Using Kingswaysoft Components with BIML
Now that we have covered the basics of BIML, we will go through an example of how our components can be used with any BIML or BIMLScript code. The example we are going to use in this blog post is to pull the records for some CRM entities, use an aggregate component to count all the records, then insert the total number of records along with the entity name to a database table. Therefore, for each entity, we will have a dataflow task similar to the following:
To get started, the first part of the BIMLScript is to connect to our metadata repository, which is a database table to pull the entity names we are trying to retrieve:
Then we are going to create two connection managers, one for CRM and the other for the database, which will have the table that will hold the total number of records for each entity:
Please note that the exact syntax of any of the connection managers or components can be simply generated by reverse-engineering an existing SSIS package using the Convert SSIS Packages to BIML feature.
We then continue to create the Packages tag, which will generate a single package called CountPackage, and then create an ExeceuteSQL task to make sure that we have our COUNT_TABLE created in our database:
Then we will create a for-loop to go through all the EntityNames we got from the database table and create a Dataflow task for each one of them. Also, the entity name will be embedded in the Dataflow name:
Inside the Dataflow task, we will create a CRM Source Component and link it to the CRM Connection Manager we have created earlier, and this CRM Source component will retrieve the records for the entity, which will be set by each for-loop iteration:
Next, we create an Aggregate component and link the Primary Output of the CRM Source Component to it, then between the Columns tags, we would define any column mappings. Now, we can create a Derived Column Component to create a new column, which will hold the entity name to be inserted to the database table along with the output of the Aggregate component. In the ADO NET Destination component, we would use the name of the Connection manager we have created earlier at the beginning of the BIML document to connect to the database:
We have now completed the design of each Dataflow task, but we still need to define the precedence constraint to link each Dataflow task to the one that precedes it. We can do that by using the name of the previous task and add the “.Output” part at the end, however, we had to add an exception for the first Dataflow task in order to be attached to the Execute SQL task instead:
This would complete our BIMLScript and we can go ahead and generate our SSIS package:
The generated SSIS Package will look something to the following, which is ready to be executed without any modification:
Closing Notes
The design of your BIMLScript can be more complex to automate the generation of repetitive tasks that share the same pattern, and this can save you a lot of time and effort that would be wasted otherwise designing virtually the same package multiple times. The same concept of this blog post can be applied to any of our components, which is simply reverse-engineering your existing package to get the exact syntax you need to work with, then embed your C# code to generate your BIML code automatically.
BIML Package
The sample BIML package we used in this blog post is available for download here.