Hadi Fadlallah
Adding a C# file to the solution

Using external C# script files within Biml

March 17, 2020 by

In the previously published article, Extending Biml with C# scripts, we have explained how to use VB or C# scripts within the Biml code to prevent doing repetitive development work. But in this solution, the C# scripts and classes are only available within a single file while we may need to use them in many.

In this article, we will explain how to store these VB or C# scripts within external files and use them within Biml scripts. Also, we will mention how to create extension methods within these C# scripts.

Adding VB or C# scripts

To add external scripts, we must go to the “Extensions” menu strip, “BimlExpress” and click on “Add New C# file” or “Add New VB file” based on the language we need to use:

Adding a C# file to the solution

Figure 1 – Adding a new C# file

After clicking on this button, a new C# file is added within the solution:

C# file within added within the solution explorer

Figure 2 – C# file added within the solution

Defining a method within the C# class

In this example, we will use this C# class to define a method that generates a global identifier (GUID) to be used within a Derived Column Transformation. We open the file and import the System namespace. Then we add the following code:

Using the C# file within Biml

To get started, we will use the Biml code we developed in the Extending Biml with C# scripts article.

First, we need to import the C# file using a directive block placed outside the main <Biml> element as following:

Then when we need to use any method defined within this file, we should write the class and method name (i.e., MyClass.GetNewGuid()).

Back to the Biml code that we have developed previously. If we need to use the C# method within the Derived Column expression, we should use a text block where we implement this method:

If we take a look in the compiled code window, we can see that a new global identifier is evaluated for the derived column transformation (Note that these examples may not work in the real world, since the GUID generated will not change for all rows imported and it is not generated per row).

showing how helper method is evalauted when using whitin a derived colum expression

Figure 3 – Showing method returned value after compilation

As we can see from the code, we have manually added the schema name in the OLE DB source and OLE DB destination components, since the “con.GetDatabaseSchema()” returns only the table name.

We will now add a C# script that takes the TableNode as a parameter and return a fully qualified name (schema + table):

Now, we will implement this method within the Biml code as the following:

If we check the compiled code window, we will see that we obtain the fully qualified table name:

showing how helper method is evalauted in the compiled Biml code

Figure 4 – Method returning fully qualified table name

Creating an extension method

In the previous example, we have created a method related to the TableNode object within a different class. But what if we have added multiple external script files, tracking methods within classes will be harder each time a new class is added. To solve this issue, we can create external methods that will appear in the TableNode class (extension method). If you are not familiar with extension methods, you can refer to one of the following articles to learn more about this technology:

To create an extension method, we should add “this” keyword before the method parameter data type as follows:

The whole code should look like the below:

Now, within the Biml code, we can call this method using the following line of text block:

From the screenshot below, we can see how this method is evaluated after compilation:

showing how extension method is evalauted in the compiled Biml code

Figure 5 – Extension method evaluation

Conclusion

In this article, we have explained how to use external C# scripts within the Biml code (using Helper classes and methods), also we have mentioned how to convert these helper methods to extension methods.

For now, I think this is the last article about using Biml to generate and control SQL Server Integration Services (SSIS) packages within this series. If you are looking for a good reference to learn Biml, you can refer to the links that we provided in the Getting started with Biml article, or you can refer to the amazing blog of Catherine Wilhelmsen, which is one of the leading experts in this domain.

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

Hadi Fadlallah
603 Views