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:
Figure 1 – Adding a new C# file
After clicking on this button, a new C# file is added within the solution:
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
using System.Collections.Generic; using System.Linq; using System.Text; using System; using Varigence.Biml.Extensions; public class MyClass { public static string GetNewGuid() { return Guid.NewGuid().ToString("N"); } } |
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:
|
1 |
<#@ code file="Code1.cs" #> |
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:
|
1 2 3 4 5 |
<DerivedColumns Name="Derived Column"> <Columns> <Column Name="NewGuid" DataType="String"><#=MyClass.GetNewGuid()#></Column> </Columns> </DerivedColumns> |
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).
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):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
using System.Collections.Generic; using System.Linq; using System.Text; using Varigence.Languages.Biml.Table; using Varigence.Biml.Extensions; public class MyClass { public static string GetFullyQualifiedName(AstTableNode table) { return "[" + table.Schema.Name + "].[" + table.Name + "]"; } } |
Now, we will implement this method within the Biml code as the following:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
<Dataflow Name="Data Flow Task"> <Transformations> <OleDbSource Name="OLE DB Source" ConnectionName="SourceOleDbConnection"> <ExternalTableInput Table="<#=MyClass.GetFullyQualifiedName(table)#>" /> </OleDbSource> <DerivedColumns Name="Derived Column"> <Columns> <Column Name="MigrationDate" DataType="DateTime">GETDATE()</Column> </Columns> </DerivedColumns> <OleDbDestination Name="OLE DB Destination" ConnectionName="DestinationOleDbConnection"> <ExternalTableOutput Table="<#=MyClass.GetFullyQualifiedName(table)#>" /> </OleDbDestination> </Transformations> </Dataflow> |
If we check the compiled code window, we will see that we obtain the fully qualified table name:
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:
|
1 |
public static string GetFullyQualifiedName(this AstTableNode table) |
The whole code should look like the below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
using System.Collections.Generic; using System.Linq; using System.Text; using Varigence.Languages.Biml.Table; using Varigence.Biml.Extensions; public static class MyClass { public static string GetFullyQualifiedName(this AstTableNode table) { return "[" + table.Schema.Name + "].[" + table.Name + "]"; } } |
Now, within the Biml code, we can call this method using the following line of text block:
|
1 |
<#=table.GetFullyQualifiedName()#> |
From the screenshot below, we can see how this method is evaluated after compilation:
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
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
- An overview of SQL Server monitoring tools - December 12, 2023
- Different methods for monitoring MongoDB databases - June 14, 2023
- Learn SQL: Insert multiple rows commands - March 6, 2023