By: Koen Verbeeck
Overview
In this part of the tutorial we’ll look at BimlScript. This is a mixture of C# and Biml code and it allows us to create multiple objects at once.
Automating Biml
Creating many SSIS package manually is tedious. Writing Biml code manually is tedious as well. However, by plugging in C# code nuggets into the Biml code, we can automate large portions of the Biml code. Let’s illustrate with an example.
In the following Biml script we have inserted a simple for loop that starts at 1 and ends at 5. At each iteration of the loop, we create a new package.
Inside the loop, regular Biml code is used to create package elements. The combination of C# and regular Biml is called BimlScript. When the code is saved, the preview window shows the expanded Biml. This is the Biml code that you receive when all of the C# code is validated, compiled and executed:
Using C# is extremely powerful, since all of the power the language can be used to generate Biml code. You can use all of the assemblies (you might need to add some references), functions, classes etc. that are at your disposal. You can read from databases, write to files, create lists and so on. Note that it is also possible to use Visual Basic instead of C#. This needs to be indicated at the top of the Biml file:
As with XML, C# has also special characters that need escaping.
| Character | Escape code |
|---|---|
| double quote " | \” |
| single quote ' | \’ |
| backslash | \\ |
As in any programming language, you can put comments in your code, which is a good practice to document your Biml code.
Directives and Code Nuggets
The programming language elements within a Biml file are referred to as directives and code nuggets. Directives are instructions to the Biml engine on how to process the Biml file. They are enclosed between <#@ and #>. In the previous paragraph we showed the language directive: to switch to the VB programming language. You also use directives to import assemblies or namespaces. Some examples:
There are different types of code nuggets. The first one is the control code nugget which has the <# and #> tags. This nugget typically contains standard C# code.
Another nugget is the text code nugget. This nugget typically evaluates an expression and return it as a string. It has the <#= and #> tags. In the example above such nugget retrieves the value of the variable i.
The class code nugget is used to define programming objects, such as classes or methods. They are commonly used for reusing C# code within your Biml solution. They have the <#+ and #> tags. The following example creates a function called NumberOfColumns which returns the number of columns of a table.
To generate multiple packages that contain useful content, the C# code needs actual metadata to work with. In the next chapter, we’ll find out where we can retrieve that metadata.
Additional Information
- The BimlScript website has tons of tutorials, code snippets and walkthroughs on how to create Biml scripts.
