Why Bother with Biml?

Also check out an updated post on - More reasons you should adopt Biml

If you haven’t heard of Biml before, it stands for Business Intelligence Markup Language. Basically it allows you to describe Microsoft Business Intelligence objects (SSIS Packges, SSAS Cubes) in XML which are then generated into the actual objects.

At first reading you would be forgiven for thinking that this is just going to add another step in an already long winded process to build a data warehouse. That is what I used to think until I saw it in action a few weeks ago at SQLRally.

What does it look like?

Here is a trivial example which will just generate two project level connection managers, and one SSIS package which has a single Execute SQL task.

As you can see, the XML is very expressive which makes it very easy to read and understand. After running the Biml generation tool the following appears (as if by magic) in the Integration Services project.

And the package itself looks like this:

Mixing C# and Biml

What makes Biml really powerful is the ability to mix C# with the Biml code to generate dynamic packages. Take another trivial example:

You can see the c# code in lines 7, 8 and 19. This Biml script will loop through the element five times and in turn create five packages.

So why bother?

In addition to being able to embedded trivial looping mechanisms, you can get a lot more creative and sophisticated. For example, you can execute sql queries and then loop over the results. This would allow you to generate dynamic packages based on some metadata store.

At SQLRally Davide Mauri went into a lot of depth on implementing design patterns with Biml to automate the monkey work, and I think he absolutely right! He has very helpfully posted his slides and demo code on his blog, and I would highly recommend that you have a read through as it is very educational.

What I’m working on

The shop I’m currently working for stores a lot of data in SalesForce, so I’m looking at how I could implement some of the design patterns that Davide has shown with SQL Server as the source system and use SalesForce instead.

Stay tuned! (And let me know if this would be of interest to you).

References

http://bimlscript.com - A great resource of tutorials, from getting started to in-depth

http://www.varigence.com/Products/Biml/Capabilities - These are the original developers, they have their own IDE

http://bidshelper.codeplex.com - This handy addon for BIDS / SSDT BI enables you to compile your Biml into SSIS packages. If you are a Microsoft BI developer you should really already be using this!