Debugging Biml

At this point, I don't even know who to give credit for on this tip/trick as I've seen it from so many luminaries in the field. This mostly applies to BimlScript debugging within the context of BIDS Helper/BimlExpress.

Using tooling is always a trade-off between time/frustration and monetary cost. BIDS Helper/BimlExpress are free so you're prioritizing cost over all others. And that's ok, there's no judgement here. I know what it's like to be in places where you can't buy the tools you really need. One of the hard parts about debugging the expanded Biml from BimlScript is you can't see the intermediate or flat Biml. You've got your Metadata, Biml and BimlScript and a lot of imagination to think through how the code is being generated and where it might be going wrong. That's tough. Even at this point where I've been working with it for four years, I can still spend hours trying to track down just where the heck things went wrong. SPOILER ALERT It's the metadata, it's always the metadata (except when it's not). I end up with NULLs where I don't expect it or some goofball put the wrong values in a field. But how can you get to a place where you can see the result? That's what this post is about.

It's a trivial bit of code but it's important. You need to add a single Biml file to your project and whenever you want to see the expanded Biml, prior to it being translated into SSIS packages, right click on the file and you'll get all that Biml dumped to a file. This recipe calls for N steps.

WritAll.biml

Right click on your project and add a Biml file called WriteAll.biml. Or whatever makes sense to you. I like WriteAll because it will generally sort to the bottom of my list of files alphabetically and that's about as often as I hope to use it.

Tiering

The first thing we need to do is ensure that the tier of this BimlScript file is greater than any other asset in the project. We will do that through the directive of template tier="N" where N is a sufficiently large number to ensure we don't have any natural tiers greater than it.

I'll also take this as an opportunity to impart a lesson learned from writing Apple Basic many, many years ago. Do not use one as the step value for line numbers or tiers in this case. Instead, give yourself some breathing room and count by 10s because sure as you're breathing, you'll discover you need to insert something between 2 and 3 and you can't add 2.5, much less 2.25. The same lesson goes with Tiers. Tier 0 is flat biml. Tier is biml script that doesn't specify its tier. After that you're in control of your destiny.

WriteAllText

The .NET library offers a method called WriteAllText. This is the easiest method to write all the text to a file. It takes two arguments: the contents and the file name. If the file exists, it's going to overwrite it. If it doesn't exist, it will create it. Piece of pie!

Path.Combine

WriteAllText needs a path - where should we put it? I'm lazy and want to put our debugging file into a location everyone has on their computer. I can't tell you what that location will be because it's going to be different for everyone but it's guaranteed to exist. It's the %userprofile% location. On my work laptop, it's C:\Users\BillFellows. On my home computer, it's C:\users\bfellows At the governmental agency, my home directory was actually on a network somewhere so it was just H:\ All you have to do is open up windows explorer and type %userprofile% and that's where we'll write this file.

If you are ever putting paths together through string building, please stop. It's a pain to deal with escaping the path separators, \, and it can be difficult to be consistent as some will build a path with a trailing slash and others won't. Stop trying to figure out that logic and use Path.Combine

We'll combine the special path location with a file name, Debug.biml and get a perfectly valid path for our output file. If you don't want overkill, then just make a hardcoded path.

GetBiml

Every object in the Biml universe supports the GetBiml method. What's amazingly powerful about this function is that it has the ability to call the GetBiml method on all the items under it. You don't have to worry about how many packages exist and how many Tasks and Variables and Events exist under them. Just call the appropriate parent level GetBiml method and object inheritance takes care of the rest.

RootNode

The RootNode is the base of everything in Biml so by calling its GetBiml method, you'll get the Biml for all the derived objects within the project. Eureka! That's what we wanted! And since we won't call this until everything else has completed, via tiering property, we will get our flattened Biml

WriteAll.biml

Putting all that together, we get a file that looks like this

<#@ template tier="999"#>
<#
System.IO.File.WriteAllText(System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.UserProfile), "Debug.biml"), RootNode.GetBiml());
#>

If I want to see what's being built in ComplexBimlScript, I simply multiselect it and WriteAllText and I'll get a Debug.biml file. From there, I generally open Debug.biml in a separate SSIS project and Check Biml For Errors and it's much easier to zip to the error. Then it's a matter of trying where that bad code is generated back to the correct bit of Biml.

Closing thoughts

If you get some really weird error going on inside your BimlScript, this debug file will appear to be an empty Biml tag. In that case, it's probably your metadata so start breaking your solution down until it's working and then gradually add complexity back into it.

p.s.

An alternative thought on tearing your code apart until you find it works would be to use this WriteAllText approach but do it per tier. That would allow you to inspect the compilation at ever step in the process to discern where things went wrong.