T-SQL Tuesday #99 is hosted by Aaron Bertrand (@AaronBertrand) and the topic is Dealer’s Choice. What does that mean? Aaron wanted us to choose between two topics: write about something we are passionate about outside the SQL Server community, or write about T-SQL bad habits and best practices. I’m too afraid to argue with Aaron about T-SQL, so I chose the first option ;)
But what did I want to write about? Truth be told, I’m pretty boring. I don’t have any real hobbies outside of tech, just a few interests. I first considered writing about hiking. I need to disconnect completely once in a while, and my favorite way of doing that is to get sweaty while walking for hours up a mountain or waterfall. My Instagram (@cathrinesqueee) is my highlight reel, full of pictures and happy memories from my trips.
However, instead of embedding all the pictures that are already on Instagram, I decided to write about something completely different. Something obscure. Something fun and geeky from way before I got involved in the SQL Server community. Something from my good old teenage fangirling days…
It’s that time of year again… The largest, coolest, geekiest, most awesome party of the year is just around the corner! Oh, and there’s this conference attached to the party as well. SQLBits 2018: Magic Edition. You may have heard of it? :D
SQLBits is the largest SQL Server and Microsoft Data Platform conference in Europe. It has 4 days of training and over 170 sessions by both the Microsoft Product Group and community speakers. It’s a professional conference in all ways, and it’s organized entirely by a group of dedicated volunteers. That’s right, it’s a pure community event, and it’s fantastic.
I was a speaker and volunteer at SQLBits 2015 (Superhero Edition), a precon speaker at SQLBits 2016 (Space Edition), a PASS representative at SQLBits 2017 (Disco Edition), and I’m very happy to go back to where I started as a speaker and volunteer at SQLBits 2018 (Magic Edition). I was actually really bummed when I wasn’t a volunteer, it’s so much fun helping out to make it a smooth conference for attendees and getting to know all the other wonderful volunteers. This year, I’m back! Yay :)
Biml objects have many built-in attributes. For example, all Tables have SchemaName and all Packages have ProtectionLevel. When your Biml solution starts to grow, you will quickly see the need for adding additional metadata that can be used in other Biml files. A common use case in Data Warehouse Staging projects is to store the source schema and source table name on your staging table objects. This allows you to use the source metadata in a higher tier Biml file that generates the SSIS packages to load the tables. To store and use this additional metadata, you can use Biml Annotations or ObjectTags.
Biml Annotations and ObjectTags are both Key/Value pairs. Annotations are String/String pairs intended for storing simple text metadata, while ObjectTags are String/Object pairs that can also store more complex metadata in .NET objects.
Do you want to learn more Biml? Have you always wanted to visit Chicago? Are you free on March 16-17? Then check out this amazing, once-in-a-lifetime offer just for you! …
Ok, ok, ok, I may have gotten a little carried away :) But I am excited to announce that I will be presenting a precon at SQLSaturday Chicago 2018! The precon will be held on Friday, March 16th, and is called Level Up! From Basic Biml to Automated Data Warehouse Frameworks. I will also present a general session on Saturday, March 17th.
Many Biml solutions start off very simple, with just a single Biml file that generates a few SSIS packages. Most developers quickly see the need for a more complex solution for multiple sources. One way to reuse code and apply the Don’t Repeat Yourself software engineering principle in Biml is to use Tiered Biml Files.
In addition to using Tiered Biml Files, there are four other main ways you can avoid repeating your Biml code: