BIML, Part 1 — The Introduction

I like SQL Server Integration Services a lot. It abuses me far too often with unclear error messages, finicky metadata requirements, and maddening inconsistency, but when it works, drag-and-drop ETL is a thing of beauty. One thing which drag-and-drop ETL does not do well, however, is scale. When I’m working on a single package, bringing elements on the grid can work, but if you want me to do the same thing for dozens of data flows or packages, that’s a tall order. This is where BIML comes in.

BIML is the Business Intelligence Markup Language, an XML-based language which builds the XML that Integration Services uses to understand what you want to move and where it should go. BIML is also extremely useful in the Analysis Services space, but I have to admit ignorance on that front. Syntactically, BIML is like programming old-style ASP or more modern Razor, in that you have your XML-based main language (XHTML in our analogy) and a way of introducing C# to modularize and repeat templates.

One of the best places to learn BIML is BIMLScript.com, which has a host of tutorials and walkthroughs, including video introductions. To follow along, you can purchase Mist—something which I’m still trying to get my company to do, but it is rather pricey per user—or download BIDS Helper, an open-source Visual Studio add-in which gives you the power of BIML.

Over the next few posts, I’m going to walk through my personal workflow for using BIML on a particular project which migrates client data from one copy of a database to another. This set of posts will turn into a presentation that I hope to give at a few user groups and eventually SQL Saturdays. Stay tuned!

Advertisement