Getting Started with BimlScript - Part 2

gravatar

Paul S. Waters

Part 2: Learn to create BimlScript by incorporating BimlScript code nuggets in a Biml file. These code blocks make it possible to auto-generate multiple packages, while conditionally adding tasks to some packages but not others.

published 09.10.12

last updated 10.02.12


Share

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

You are not authorized to comment. A verification email has been sent to your email address. Please verify your account.

Comments

gravatar

Randy

4:59pm 05.22.14

In the first example, I get an error; Name cannot begin with the '1' character, hexadecimal 0x31. Line 3, position 33. To be sure I didn't type something wrong, I copied the whole code block from your site into my biml file. Same error.

Please help

gravatar

Nghi

10:12pm 06.17.14

Hi Randy,

I'm not sure if you've resolved your problem, but maybe this will help anyone else that has the same issue.

When you copy the code block into your biml file, for some reason it adds an extra quotation mark after MyPackages and puts <#=i#>" ConstraintMode="Linear"> on a new line. If you remove that extra quotation mark, it should work.

gravatar

Nghi

10:14pm 06.17.14

Actually, it looks like the author mentions this in Part 3 and provides a remedy: http://bimlscript.com/Walkthrough/Details/45

gravatar

Randy

6:17pm 08.20.14

Howdy all; circling back to close out... I tried this again and this time it works.

Obviously first time around I must have mis-typed something somewhere. But it works

now and that's the important thing. Tx.