What are the best methods for generating Biml and what language should you use? Before I dive into this, let me begin with a disclaimer. I applaud anyone working with Biml to improve their productivity and eliminate needless, repetitive work. One of the primary advantages of Biml is its flexibility, which allows people to generate it many different ways to solve problems. I’m going to briefly discuss some concepts involved in the process and common ways I see it being generated. I’m also going to make recommendations that I’ve found work well in most circumstances. However, remember that this is only my opinion, and your mileage may vary. Let’s start by discussing two basic strategies for creating Biml, which I refer to as Scripting Biml vs. BimlScript.
What exactly do I mean by “Scripting Biml?” Biml is simply an XML Language and therefore can be produced with many different tools and languages. When I talk about “Scripting Biml,” I’m referring to using some kind of tool or language to generate static Biml from outside of the “text” itself. This could take the form of XML language generation or simple string manipulation. I myself started down this route because string manipulation was familiar and comfortable to me. However, I quickly found that this method is very limiting and becomes unwieldy when attempting any complex scripting. I’ve listed several “Scripting Biml” examples, and included links for further research as well as a brief explanation of the methods/strategies being leveraged.
At PASS Summit 2013, Allen White (Blog/Twitter) presented Automate Your ETL Infrastructure with SSIS and PowerShell, which is available for streaming at that link. Allen demonstrates how to use PowerShell and Server Management Objects (SMO) to generate Biml. Be forewarned, unless you’re really into PowerShell, this video may not be for the faint of heart.
.Net Language – C#
One of my clients created an open source project called BimlGen, which is available on GitHub. BimlGen imports SQL Server database tables into Biml, and converts SQL Server Extended Properties into Biml Annotation Tags. Admittedly, it needs work, but it’s free and open sourced. I’m hoping that folks in the community will work on it to improve and extend its functionality. Simply enter the Server and Database Names, change the output folder if you like, and click Generate. The output folder will open and you should find a single Biml file which contains your Biml assets.
BimlScript is created by embedding C# or VB.Net code within the Biml itself. Once you grasp the basics of BimlScript, which admittedly is not a trivial undertaking, your productivity will skyrocket. BimlScript is analogous to HTML, which is also a markup language, and ASP.Net. BimlScript allows the programmer to embed nuggets of C# or VB.Net code inside Biml to dynamically generate Biml assets, much the same way that ASP.Net dynamically generates web pages. These nuggets often include LINQ (Language Integrated Query) which is compatible with either C# or VB.Net. If all of this is new to you, don’t worry, I’ll explain all of this in more detail in a later post.
Should I use C# or VB.Net?
Let me preface this by saying that I started coding BASIC long ago on a TRS-80 machine, and I’ve been coding in BASIC ever since. However, when I started down the BimlScript path, I drank the C# Kool-Aid for the following reasons:
- The vast majority of the BimlScript samples available are written in C#. This fact alone was enough to get me to learn C#.
- C# with LINQ just seems cleaner once you get over C#’s affinity for semi-colons and curly braces. However, those C# curly braces tend to stand out better than VB statements embedded inside Biml.
- Mist doesn’t support Intellisense for VB.Net yet, but that functionality will be added soon.
- Arguably, C# programming skills are in more demand and worth more money than VB.Net.
That being said, if I had a team of skilled VB.Net programmers, I would not invest the resources to teach them C# just to work with BimlScript.
Types of Biml Scripts
There are three basic types of BimlScripts which support both C# and VB.Net.
- Expandable – This is the most common type and is the only type supported by BidsHelper. These types of scripts are executed “manually” by the user.
- Live – If you choose to purchase Mist, Biml Scripts can be automatically run in the background, primarily for convenience. One of the nice things about Mist is I can use the same script as either Expandable or Live at my choosing.
- Transformers – These scripts change existing objects like SSIS Packages according to code logic and metadata. This is a Mist only option as well.
The vast majority of the scripts I share will be expandable and will use C#/LINQ. In the next post, I’ll explain exactly how I use metadata to control my BimlScript framework sample.