BIML from the ground up
I’ve been doing a lot of BIML study recently. I’ve been a big fan of BIML for the last couple years, but haven’t had a lot of time to sit and really dive deep on how to write it or how to extend it with C#. One of the most valuable resources I’ve come upon in this effort has been Stacia Misner Varga‘s post on ‘Building Blocks of BIML‘ on PluralSight. This post focuses on one of the coolest things I learned in that video series.
What the heck is BIML doing?
One of the first things I started wondering about as I got used to reading OPC (other peoples code) is just EXACTLY what is BIML doing at any given point in the code. You can make some educated guesses based on the SSIS packages (in my case I’m exclusively interested in BIML for SSIS but of course it can do a lot more than that), but it’s easy to get lost, especially when there’s a lot of BIML script and some of it is only used to establish a data model in memory or to create / fill variables that will be used in SSIS. I was delighted to discover the following piece of code that can show you exactly what BIML is doing based on the code you are writing.
string xBiml = RootNode.GetBiml();
XmlDocument xmlDoc = new XmlDocument();
string sFile = @"C:\costello\ssis-biml\ssisBIML.xml";
<#@ template tier="100" #>
<#@ import namespace="Varigence.Biml.CoreLowerer.SchemaManagement" #>
<#@ import namespace="System.Xml" #>
Simply copy the code above into a new .BIML file and name it whatever you like. I call my file save-compiled-biml.biml. Full disclosure: I did not write this code myself. I did a little digging around on the internet and it looks like Marco Schreuder was the original author, but I might be mistaken. You can check out his original blog post or see an early version of this code on GitHubGist.
To use this code, select the file you created from the code snip above AND the .BIML files that you are trying to see the output of at the same time. When you test your .BIML files (or generate SSIS packages) a .xml file will be saved in the location you specified as the value of the sFile variable in the code.
My first thought was that every code might append to the output file that save-compiled-biml.biml creates, but some further exploration proved that each time the output file is over written. I usually just double click on the ssisBIML.xml file the code creates and it opens in a web browser.
What you get:
The output file is (imo) absolutely fascinating. What you see here is essentially the recipe that the BIML compiler will use to create your SSIS package(s). If you’re BIML includes BIML script to loop through every table in a database and create a new package for each you’ll see all the XML that will ultimately be used to create those SSIS packages in ssisBIML.xml. It’s a great way to see exactly what BIML is doing behind the scenes without a lot of the complexity of interpreting the BIML script in your head as your read the code.