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:
- Specifying the script language (C# or VB) and import needed assemblies
- Retrieve tables metadata from AdventureWorks database
- Loop over tables and generate a package for each table
- Define connection managers within the script
- 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:
|
1 2 3 4 |
<#@ template language="C#" hostspecific="true"#> <#@ import namespace="System.Data" #> <Biml xmlns="http://schemas.varigence.com/biml.xsd"> </Biml> |
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:
|
1 2 |
<# var con = SchemaManager.CreateConnectionNode("SourceConnection","Data Source=.;Initial Catalog=AdventureWorks2017;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;"); #> <# var metadata = con.GetDatabaseSchema(new List<string>{"Person"},null,ImportOptions.ExcludeViews); #> |
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:
|
1 2 3 4 5 6 |
<Biml xmlns="http://schemas.varigence.com/biml.xsd">. <Connections> <Connection Name="SourceOLEDBConnection" ConnectionString="Data Source=.;Initial Catalog=AdventureWorks2017;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" /> <Connection Name="DestinationOLEDBConnection" ConnectionString="Data Source=.;Initial Catalog=tempdb;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" /> </Connections> </Biml> |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
<Biml xmlns="http://schemas.varigence.com/biml.xsd"> <Connections> <Connection Name="SourceOLEDBConnection" ConnectionString="Data Source=.;Initial Catalog=AdventureWorks2017;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" /> <Connection Name="DestinationOLEDBConnection" ConnectionString="Data Source=.;Initial Catalog=tempdb;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" /> </Connections> <Packages> <# foreach (var table in metadata.TableNodes.Where(x => x.Schema.Name == "Person")) { #> <Package Name="Extract_<#=table.Name#>"> </Package> <# } #> </Packages> </Biml> |
Note that we have used a Text block to evaluate the package name as expression:
|
1 |
<Package Name="Extract_<#=table.Name#>"> |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
<Tasks> <Dataflow Name="Data Flow Task"> <Transformations> <OleDbSource Name="OLE DB Source" ConnectionName="SourceOleDbConnection"> <ExternalTableInput Table="[Person].[<#=table.Name#>]" /> </OleDbSource> <DerivedColumns Name="Derived Column"> <Columns> <Column Name="MigrationDate" DataType="DateTime">GETDATE()</Column> </Columns> </DerivedColumns> <OleDbDestination Name="OLE DB Destination" ConnectionName="DestinationOleDbConnection"> <ExternalTableOutput Table="[Person].[<#=table.Name#>]" /> </OleDbDestination> </Transformations> </Dataflow> </Tasks> |
Generating package
The whole BimlScript code should look like:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
<#@ template language="C#" hostspecific="true"#> <#@ import namespace="System.Data" #> <# var con = SchemaManager.CreateConnectionNode("SourceConnection","Data Source=.;Initial Catalog=AdventureWorks2017;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;"); #> <# var metadata = con.GetDatabaseSchema(new List<string>{"Person"},null,ImportOptions.ExcludeViews); #> <Biml xmlns="http://schemas.varigence.com/biml.xsd"> <Connections> <Connection Name="SourceOleDbConnection" ConnectionString="Data Source=.;Initial Catalog=AdventureWorks2017;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" /> <Connection Name="DestinationOleDbConnection" ConnectionString="Data Source=.;Initial Catalog=tempdb;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" /> </Connections> <Packages> <# foreach (var table in metadata.TableNodes) { #> <Package Name="Extract_<#=table.Name#>"> <Tasks> <Dataflow Name="Data Flow Task"> <Transformations> <OleDbSource Name="OLE DB Source" ConnectionName="SourceOleDbConnection"> <ExternalTableInput Table="[Person].[<#=table.Name#>]" /> </OleDbSource> <DerivedColumns Name="Derived Column"> <Columns> <Column Name="MigrationDate" DataType="DateTime">GETDATE()</Column> </Columns> </DerivedColumns> <OleDbDestination Name="OLE DB Destination" ConnectionName="DestinationOleDbConnection"> <ExternalTableOutput Table="[Person].[<#=table.Name#>]" /> </OleDbDestination> </Transformations> </Dataflow> </Tasks> </Package> <# } #> </Packages> </Biml> |
You can check the Biml generated from this script in the preview window available in the Visual studio editor:
Figure 1 – Script editor
The following code is generated from the script above:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 |
<Biml xmlns="http://schemas.varigence.com/biml.xsd"> <Connections> <Connection Name="SourceOleDbConnection" ConnectionString="Data Source=.;Initial Catalog=AdventureWorks2017;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" /> <Connection Name="DestinationOleDbConnection" ConnectionString="Data Source=.;Initial Catalog=tempdb;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" /> </Connections> <Packages> <Package Name="Extract_Address"> <Tasks> <Dataflow Name="Data Flow Task"> <Transformations> <OleDbSource Name="OLE DB Source" ConnectionName="SourceOleDbConnection"> <ExternalTableInput Table="[Person].[Address]" /> </OleDbSource> <DerivedColumns Name="Derived Column"> <Columns> <Column Name="MigrationDate" DataType="DateTime">GETDATE()</Column> </Columns> </DerivedColumns> <OleDbDestination Name="OLE DB Destination" ConnectionName="DestinationOleDbConnection"> <ExternalTableOutput Table="[Person].[Address]" /> </OleDbDestination> </Transformations> </Dataflow> </Tasks> </Package> <Package Name="Extract_AddressType"> <Tasks> <Dataflow Name="Data Flow Task"> <Transformations> <OleDbSource Name="OLE DB Source" ConnectionName="SourceOleDbConnection"> <ExternalTableInput Table="[Person].[AddressType]" /> </OleDbSource> <DerivedColumns Name="Derived Column"> <Columns> <Column Name="MigrationDate" DataType="DateTime">GETDATE()</Column> </Columns> </DerivedColumns> <OleDbDestination Name="OLE DB Destination" ConnectionName="DestinationOleDbConnection"> <ExternalTableOutput Table="[Person].[AddressType]" /> </OleDbDestination> </Transformations> </Dataflow> </Tasks> </Package> <Package Name="Extract_BusinessEntity"> <Tasks> <Dataflow Name="Data Flow Task"> <Transformations> <OleDbSource Name="OLE DB Source" ConnectionName="SourceOleDbConnection"> <ExternalTableInput Table="[Person].[BusinessEntity]" /> </OleDbSource> <DerivedColumns Name="Derived Column"> <Columns> <Column Name="MigrationDate" DataType="DateTime">GETDATE()</Column> </Columns> </DerivedColumns> <OleDbDestination Name="OLE DB Destination" ConnectionName="DestinationOleDbConnection"> <ExternalTableOutput Table="[Person].[BusinessEntity]" /> </OleDbDestination> </Transformations> </Dataflow> </Tasks> </Package> <Package Name="Extract_BusinessEntityAddress"> <Tasks> <Dataflow Name="Data Flow Task"> <Transformations> <OleDbSource Name="OLE DB Source" ConnectionName="SourceOleDbConnection"> <ExternalTableInput Table="[Person].[BusinessEntityAddress]" /> </OleDbSource> <DerivedColumns Name="Derived Column"> <Columns> <Column Name="MigrationDate" DataType="DateTime">GETDATE()</Column> </Columns> </DerivedColumns> <OleDbDestination Name="OLE DB Destination" ConnectionName="DestinationOleDbConnection"> <ExternalTableOutput Table="[Person].[BusinessEntityAddress]" /> </OleDbDestination> </Transformations> </Dataflow> </Tasks> </Package> <Package Name="Extract_BusinessEntityContact"> <Tasks> <Dataflow Name="Data Flow Task"> <Transformations> <OleDbSource Name="OLE DB Source" ConnectionName="SourceOleDbConnection"> <ExternalTableInput Table="[Person].[BusinessEntityContact]" /> </OleDbSource> <DerivedColumns Name="Derived Column"> <Columns> <Column Name="MigrationDate" DataType="DateTime">GETDATE()</Column> </Columns> </DerivedColumns> <OleDbDestination Name="OLE DB Destination" ConnectionName="DestinationOleDbConnection"> <ExternalTableOutput Table="[Person].[BusinessEntityContact]" /> </OleDbDestination> </Transformations> </Dataflow> </Tasks> </Package> <Package Name="Extract_ContactType"> <Tasks> <Dataflow Name="Data Flow Task"> <Transformations> <OleDbSource Name="OLE DB Source" ConnectionName="SourceOleDbConnection"> <ExternalTableInput Table="[Person].[ContactType]" /> </OleDbSource> <DerivedColumns Name="Derived Column"> <Columns> <Column Name="MigrationDate" DataType="DateTime">GETDATE()</Column> </Columns> </DerivedColumns> <OleDbDestination Name="OLE DB Destination" ConnectionName="DestinationOleDbConnection"> <ExternalTableOutput Table="[Person].[ContactType]" /> </OleDbDestination> </Transformations> </Dataflow> </Tasks> </Package> <Package Name="Extract_CountryRegion"> <Tasks> <Dataflow Name="Data Flow Task"> <Transformations> <OleDbSource Name="OLE DB Source" ConnectionName="SourceOleDbConnection"> <ExternalTableInput Table="[Person].[CountryRegion]" /> </OleDbSource> <DerivedColumns Name="Derived Column"> <Columns> <Column Name="MigrationDate" DataType="DateTime">GETDATE()</Column> </Columns> </DerivedColumns> <OleDbDestination Name="OLE DB Destination" ConnectionName="DestinationOleDbConnection"> <ExternalTableOutput Table="[Person].[CountryRegion]" /> </OleDbDestination> </Transformations> </Dataflow> </Tasks> </Package> <Package Name="Extract_EmailAddress"> <Tasks> <Dataflow Name="Data Flow Task"> <Transformations> <OleDbSource Name="OLE DB Source" ConnectionName="SourceOleDbConnection"> <ExternalTableInput Table="[Person].[EmailAddress]" /> </OleDbSource> <DerivedColumns Name="Derived Column"> <Columns> <Column Name="MigrationDate" DataType="DateTime">GETDATE()</Column> </Columns> </DerivedColumns> <OleDbDestination Name="OLE DB Destination" ConnectionName="DestinationOleDbConnection"> <ExternalTableOutput Table="[Person].[EmailAddress]" /> </OleDbDestination> </Transformations> </Dataflow> </Tasks> </Package> <Package Name="Extract_Password"> <Tasks> <Dataflow Name="Data Flow Task"> <Transformations> <OleDbSource Name="OLE DB Source" ConnectionName="SourceOleDbConnection"> <ExternalTableInput Table="[Person].[Password]" /> </OleDbSource> <DerivedColumns Name="Derived Column"> <Columns> <Column Name="MigrationDate" DataType="DateTime">GETDATE()</Column> </Columns> </DerivedColumns> <OleDbDestination Name="OLE DB Destination" ConnectionName="DestinationOleDbConnection"> <ExternalTableOutput Table="[Person].[Password]" /> </OleDbDestination> </Transformations> </Dataflow> </Tasks> </Package> <Package Name="Extract_Person"> <Tasks> <Dataflow Name="Data Flow Task"> <Transformations> <OleDbSource Name="OLE DB Source" ConnectionName="SourceOleDbConnection"> <ExternalTableInput Table="[Person].[Person]" /> </OleDbSource> <DerivedColumns Name="Derived Column"> <Columns> <Column Name="MigrationDate" DataType="DateTime">GETDATE()</Column> </Columns> </DerivedColumns> <OleDbDestination Name="OLE DB Destination" ConnectionName="DestinationOleDbConnection"> <ExternalTableOutput Table="[Person].[Person]" /> </OleDbDestination> </Transformations> </Dataflow> </Tasks> </Package> <Package Name="Extract_PersonPhone"> <Tasks> <Dataflow Name="Data Flow Task"> <Transformations> <OleDbSource Name="OLE DB Source" ConnectionName="SourceOleDbConnection"> <ExternalTableInput Table="[Person].[PersonPhone]" /> </OleDbSource> <DerivedColumns Name="Derived Column"> <Columns> <Column Name="MigrationDate" DataType="DateTime">GETDATE()</Column> </Columns> </DerivedColumns> <OleDbDestination Name="OLE DB Destination" ConnectionName="DestinationOleDbConnection"> <ExternalTableOutput Table="[Person].[PersonPhone]" /> </OleDbDestination> </Transformations> </Dataflow> </Tasks> </Package> <Package Name="Extract_PhoneNumberType"> <Tasks> <Dataflow Name="Data Flow Task"> <Transformations> <OleDbSource Name="OLE DB Source" ConnectionName="SourceOleDbConnection"> <ExternalTableInput Table="[Person].[PhoneNumberType]" /> </OleDbSource> <DerivedColumns Name="Derived Column"> <Columns> <Column Name="MigrationDate" DataType="DateTime">GETDATE()</Column> </Columns> </DerivedColumns> <OleDbDestination Name="OLE DB Destination" ConnectionName="DestinationOleDbConnection"> <ExternalTableOutput Table="[Person].[PhoneNumberType]" /> </OleDbDestination> </Transformations> </Dataflow> </Tasks> </Package> <Package Name="Extract_StateProvince"> <Tasks> <Dataflow Name="Data Flow Task"> <Transformations> <OleDbSource Name="OLE DB Source" ConnectionName="SourceOleDbConnection"> <ExternalTableInput Table="[Person].[StateProvince]" /> </OleDbSource> <DerivedColumns Name="Derived Column"> <Columns> <Column Name="MigrationDate" DataType="DateTime">GETDATE()</Column> </Columns> </DerivedColumns> <OleDbDestination Name="OLE DB Destination" ConnectionName="DestinationOleDbConnection"> <ExternalTableOutput Table="[Person].[StateProvince]" /> </OleDbDestination> </Transformations> </Dataflow> </Tasks> </Package> </Packages> </Biml> |
To generate packages, right-click on the Biml script file in the solution explorer, and click on “Generate SSIS packages”:
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:
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:
Figure 4 – Generated package control flow
Also, we can see that all components are created successfully within the 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
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