BimlScript overview

This is a followup of sorts to my posts on creating SSIS packages with the EzAPI. In these posts, I'll show how to create the same SSIS packages using Biml.

Prerequisites

The pre-reqs for using BIML to create SSIS packages is simple

• BIDSHelper

Getting started with Biml

Biml, Business Intelligence Markup Language, is a DSL for creating BI artifacts. It's an interesting proposition: learn a third language to speak a second language more efficiently. Note that Biml covers both SSIS packages and Analysis Services but for now, I'm only building a HOW TO SSIS packages. The more I've been working with this, the more I think this dog might hunt...

Open Visual Studio/BIDS/SSDT-BI and create a new project of type "Integration Services Project".

This is the SQL Server 2012 templates in Visual Studio 2012 but the beautiful thing about Biml is that it's going to work with whatever version of BIDS Helper you've installed.

Right click on your project and select "Add New Biml File".

You will now have a BimlScript.biml file in your Miscellaneous folder. You may rename it if you wish, there is no meaning between a .biml file and the output it generates.

Double click it and Visual Studio will open it and you'll be greeted with

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
</Biml>

To quote Kitty: Wow, that's really something Lex. It's freaking gone with the wind.

You'd be correct, this script won't do anything. Let's describe the Hello World equivalent of an SSIS package. We must create a Packages collection and inside that, add a Package node. Every Package you define will translate into a physical .dtsx file. Each Package node requires two attributes: Name and ConstraintMode.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package
            Name="SimpleBimlPackage"
            ConstraintMode="Linear"
        />
    </Packages>
</Biml>

A beautiful thing you should notice is the Intellisense. Perhaps it's just my age---but a good implementation of an autocomplete system is invaluable. What I really like, especially since I'm just fumbling my way through BIML, is that after defining an attribute, it no longer shows in the autocomplete list.

At this point, we still have no package, just a description of what the project as a whole and the package itself should be. Let's correct that. Right click on the BimlScript.biml file and select "Check Biml for Errors".

After a moment or two, BIDSHelper should report back "No errors or warnings were found."

Once more, right click on "BimlScript.biml" but this time chose the "Generate SSIS Packages" option. After a moment and a flicker, you will notice you now have a real SSIS package.

Examine the package from head to toe and you'll find it's devoid of anything. We now have a mirror of the two packages created with EzAPI and the straight API calls. Given the beauty of tab completion, I'm going to award Biml the fewest key strokes to generate a package.

Yup, looks like a package. One way you can differentiate a package generated from EzAPI from BIDS/SSDT from Biml or the straight .NET API is the Description property. In the case of the .NET API or Biml, the Description is only populated if you provide explicit descriptions.