Biml Hero Training, Day 1

In June of 2013, I created my first SSIS package with Biml. Three years later, I have come so far except that today was my first day of Biml Hero training. Holy cow there's a lot I have yet to learn. While I can't go into the details of the training due to the non-disclosure agreement, I wanted to take a moment and share some of the public things.

StructureEqual

The base object for all things biml, AstNode, StructureEqual method. If I understood it correctly, I could use this method to determine whether my biml representation of an object, like a table, is the same as a table that I just reverse engineered. That's pretty cool and something I'll need to play with. And remembering harder, Scott once said something about how you could use Biml as a poor man's substitute for Schema Compare. I bet this is the trick to that.

designerbimlpath

As Cathrine notes, setting this attribute will give intellisense a shove in the right direction for fragments.

Extension methods

Technically, I already picked this trick up at Cathrine's excellent session a

Topological sorting

This was an in-depth Extension method but as with any good recursive algorithm it was precious few lines of code. Why I care about it is twofold: execution dependencies and as I type that, I realize lineage tracing would also fall under this, and foreign key traversal. For the former, in my world, I find I have the best success when my SSIS packages are tightly focused on a task and I use a master/parent package to handle the coordination and scheduling of sub-package execution. One could use an extension method to discover all the packages that implement an Execute Package Task and then figure out the ordering of dependent tasks. That could save me some documentation headaches.

Foreign key traversal is something that I think would be rather clever to do in Biml. When I reverse engineer a database, I can already pull in foreign key columns. What I can't do, at least easily with the current version is to figure out what the referenced table/column is. Think about it, if I know column SellerId in FactSales is foreign keyed to column Id in DimSeller (this is exposed in sys.foreign_key_columns) and SellerName is defined as unique, I could automate the building of lookups (based on name matches). If my fact's source query looks like SELECT SaleDate, Amount, SellerName FROM stagingTable, I could see if column names matched and auto inject lookups into my fact load.

Those were my public highlights. Tomorrow's another day and I can't wait to see what we do.