Getting Started with BimlScript - Part 2
This walkthrough introduces BimlScript by demonstrating some basic programming constructs that automate the creation of SSIS packages. If you think of Biml as HTML for data warehousing and business intelligence, then BimlScript would be ASP.NET or PHP.
To start using BimlScript:
- Open the Getting Started with BimlScript folder, created in part 1 of this series of walkthroughs, in Visual Studio Code.
- In the Explorer, copy and paste My Package.biml. A copy of the file will appear.
- Rename the copy to My Packages.biml.
- Open My Packages.biml in the editor.
- Our first BimlScript will include inserting a C# for loop in the Biml file:
- Move your cursor to the end of the
<Packages>tag and press<Enter>. - Type the following to add a C# for loop:
<# for(int i = 1; i <= 5; i++) { #> - Move your cursor to the end of the
</Package>closing tag and press<Enter>. - Type the following to close the for loop:
<# } #> - Move your cursor to just before the closing quotation mark of the Package Name attribute:
<Package Name="MyPackage - Type the following:
<#=i#>
- Move your cursor to the end of the
Your Biml file should match the following:
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Packages>
<# for(int i = 1; i <= 5; i++) { #>
<Package Name="MyPackage<#=i#>" ConstraintMode="Linear">
<Tasks>
<Dataflow Name="My Dataflow">
</Dataflow>
</Tasks>
</Package>
<# } #>
</Packages>
</Biml>
Before demonstrating what this BimlScript does, you can verify that the Biml file is error free. As you type, BimlExpress flags malformed XML, Biml schema violations and BimlScript nugget syntax errors in the editor. To check the whole file:
- In the Explorer, right click My Packages.biml.
- In its context menu, click Validate Biml.
Validate Biml processes the chosen Biml file and reports any errors. If no errors are present, the status bar reports that the validation succeeded. Assuming you’ve followed the above instructions, no errors should be present in the Biml file.
Now we’re ready to generate SSIS packages from BimlScript:
- Right click My Packages.biml in the Explorer and click Build Biml. You’ll see that five new packages have been written to the
output/ssisfolder. - Open MyPackage5.dtsx. Notice that, except for the name, the package is just like the package generated from the first Biml file you created.
With these packages now created, let’s entertain a new requirement: even numbered packages must have a sequence container added before the dataflow task. To accomplish this, we’ll add an if statement, along with some Biml:
- Open My Packages.biml.
- Move your cursor to the end of the
<Tasks>tag and press<Enter>. - Add a BimlScript code nugget, containing a C# if statement, by typing:
<# if(i%2 == 0){ #> - Next, add a Sequence task by typing the following Biml under the if statement:
<Container Name="SEQ" ConstraintMode="Linear">
</Container> - Close the if statement by typing the following on the next line:
<# } #>
Your Biml file should match the following:
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Packages>
<# for(int i = 1; i <= 5; i++) { #>
<Package Name="MyPackage<#=i#>" ConstraintMode="Linear">
<Tasks>
<# if(i%2==0) { #>
<Container Name="SEQ" ConstraintMode="Linear">
</Container>
<# } #>
<Dataflow Name="My Dataflow">
</Dataflow>
</Tasks>
</Package>
<# } #>
</Packages>
</Biml>
With these changes in place, generate the SSIS packages for this Biml:
- Right click My Packages.biml in the Explorer and click Build Biml. When you do, BimlExpress asks before it overwrites the existing package files. Click Overwrite to overwrite the current SSIS packages with the new ones.
- Open the SSIS packages and observe the differences between the odd and even numbered packages.
So, what did this BimlScript code do? We used C# code to automate the addition of Biml, and thus SSIS package generation. We also introduced two types of control blocks; the standard control block delimited by <# ... #> and the expression control block delimited by <#= ... #>.
A standard control block is a nugget of program code that can control whether the Biml inside it is added to the Biml file, and thus sent to the Biml engine. In the <# if(i%2==0) { #> … <# } #> control block, the Biml is only added to the output when the if statement evaluates to true. When it’s false, the Biml is not added to the file.
Our BimlScript file also has an expression block; <#=i#>. In this expression block, we get the integer value that’s defined in the <# for(int i = 1; i <= 5; ++) { #> control block, and insert it as part of the Biml text block. Notice that we did not have to convert the i variable to a string data type; BimlScript does this for us. The output of expression blocks is always a string data type.
To learn more about control blocks, and other parts of BimlScript, please read Writing a BimlScript Template.
Next walkthrough in the Getting Started with BimlScript series: Getting Started with BimlScript - Part 3