Previous walkthrough in the Getting Started with BimlScript series: Getting Started with BimlScript - Part 1

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:

1. Open the Getting Started with BimlScript project, created in part 1 of this series of walkthroughs.
2. In Solution Explorer, copy and paste My Package.biml, under the Miscellaneous folder. A new file, named My Package 1.biml, will appear.
3. Rename My Package 1.biml to My Packages.biml.
4. Double click My Packages.biml to open it.
5. Our first BimlScript will include inserting a C# for loop in the Biml file:
   1. Move your cursor to the end of the <Packages> tag and press <Enter>.
   2. Type the following to add a C# for loop:
      <# for(int i = 1; i <= 5; i++) { #>
   3. Move your cursor to the end of the </Package> closing tag and press <Enter>.
   4. Type the following to close the for loop:
      <# } #>
   5. Move your cursor to just before the closing quotation mark of the Package Name attribute:
      <Package Name="MyPackage
   6. Type the following:
      <#=i#>

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 may be alarmed by the error squiggles that appear in the Visual Studio editor. Don’t worry; they’re safe to ignore. They’re simply a result of BIDS / SSDT lacking native support for BimlScript. However, if you don’t believe that, you can verify that the Biml file is error free:

1. In Solution Explorer, right click My Packages.biml.
2. In its context menu, click Check Biml for Errors.

Check Biml for Errors processes the chosen Biml file and reports any errors. If no errors are present, a dialog box appears indicating that no errors were found. 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:

1. Right click My Packages.biml and click Generate SSIS Packages. You’ll see that five new packages have been added under the SSIS Packages folder.
2. Double click 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:

1. Double click My Packages.biml.
2. Move your cursor to the end of the <Tasks> tag and press <Enter>.
3. Add a BimlScript code nugget, containing a C# if statement, by typing:
   <# if(i%2 == 0){ #>
4. Next, add a Sequence task by typing the following Biml under the if statement:
   <Container Name="SEQ" ConstraintMode="Linear">
</Container>
5. 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:

1. Right click My Packages.biml and click Generate SSIS Packages. When you do, a Confirm Overwritten Items dialog box will appear. Click Commit to overwrite the current SSIS packages with the new ones.
2. 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