At SQLBits 2019, I presented my Biml Tips and Tricks: Not Just for SSIS Packages! session. The session recording has been available for many months, but I only just now realized I never blogged abut it :) You can view the slide deck on my SlideShare and download my Biml Demos if you want to dig into my code examples.
Biml Tips and Tricks: Not Just for SSIS Packages! Session Recording
In a previous blog post, we looked at how to generate SQL using Biml. (If you haven’t read that post, you may want to start there and then come back here.) In this post, we will go through how to generate SELECT statements using the Biml column method GetColumnList.
Using Biml column methods
Biml column methods return code fragments. These code fragments can be used as building blocks to generate custom T-SQL statements. For example, the GetColumnList method returns a list of columns, separated by commas, that you can use in a SELECT statement. You can filter the columns and customize the output by passing parameters.
Examples of GetColumnList code fragments
If you have a table with three columns, the default output will look something like this:
[PersonID], [FirstName], [LastName]
But what if you don’t want to select all three columns? Or what if you want to use an alias for your table? No problem! The customized output can look something like this instead:
We will go through the different ways of customizing the output a little later in this post.
The first T-SQL Tuesday of 2019 is hosted by Garry Bargsley (@gbargsley), and the topic is “Automate All the Things“. Garry wants to know what this phrase means to each of us. What do we want to automate? What is our go-to technology for automation? To me, this was super easy. Surprise, surprise! It’s Biml, of course :) Since this post is part of T-SQL Tuesday, I wanted to go back to the basics and write about how you can generate SQL using Biml. But first, a little bit of background for those who are not that familiar with Biml.
In a previous blog post, we looked at how to use C#/VB Code Files in Biml. There are several benefits to moving custom C# code into separate files. It allows you to reuse that code across multiple projects and solutions. You can maintain the code in your editor of choice, taking advantage of intellisense and syntax highlighting. And finally, my personal favorite: you can create custom extension methods.
In this post, we will look at how to simplify our Biml projects by creating and using C# extension methods. We will build on the examples from the previous C#/VB Code Files in Biml blog post.
As of July 2018, there is no built-in Biml support for OData. To work with OData in Biml, you have to create a custom source and connection manager. This requires more Biml code than built-in functions like OleDbSource and may look a little overwhelming at first. But don’t worry! You don’t have to start from scratch.
In this blog post, we will first look at the properties of the OData Connection Manager and how to script it in Biml. Then, we will do the same for the OData Source. Finally, we will tie it all together and create a complete SSIS package that you can use a starting point for your own projects.
The Quick and Easy Solution
But before we dig into any code, let’s skip to the quick, easy, and timesaving solution. That’s what we all really want, right? :)
Install or upgrade to BimlExpress 2018
Create an example SSIS package using an OData Source and Connection Manager
Convert the SSIS package to Biml
As promised: quick, easy, and timesaving! The new Convert SSIS Packages to Biml feature was released in BimlExpress 2018, and it really is a lifesaver. After converting to Biml, you can simply copy and paste the code into your projects.
You may run into some bugs when you convert your SSIS packages to Biml. I ran into two issues while writing this blog post. The first was that I had to add UsesDispositions=”true” to the Source component. The second was that the data types in the Source component were prefixed with System. I have fixed both of these issues in my examples below. In addition to these issues, the converted Biml also contained some unnecessary code. Unnecessary code does not break anything, but it can make your code harder to read and maintain. Personally, I prefer my code to be as clean and simple as possible.
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.
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:
Woohoo! The Biml Book: Business Intelligence and Data Warehouse Automation is now available for pre-order from Amazon and Apress! :D
This is the first book I’ve co-authored, and I have to admit it’s a very strange feeling to see my name on the cover of a book. Am I allowed to say I’m quite proud? Oh, I’ll say it anyway. I’m proud and very honored to have written this book with such a talented group of people: Andy Leonard (@AndyLeonard), Scott Currie (@scottcurrie), Ben Weissman (@bweissman), Bill Fellows (@billinkc), Martin Andersson (@frysdisken), Peter Avenant (@PeterAvenant), Simon Peck (@biguynz), Reeves Smith (@SQLReeves), Raymond Sondak (@raymondsondak) and Jacob Alley.
What’s in The Biml Book?
The first part of the book starts with the basics: getting your development environment configured, Biml syntax, and scripting essentials.
The next part of the book guides you through the process of using Biml to build a framework that captures both your design patterns and execution management. In addition to leveraging design patterns in your framework, you will learn how to build a robust metadata store and how to package your framework into Biml bundles for deployment within your enterprise.
In the last part of the book, you will learn more advanced Biml features and capabilities, such as SSAS development, T-SQL recipes, automated documentation, and Biml troubleshooting.
When can I get The Biml Book?
Amazon says early December, but it might be available sooner. If you don’t want to wait, you can pre-order The Biml Book from Amazon or Apress right now.
Yay! This has been a long journey, and I’m so happy the other guys let me be a part of it :)
In April 2015 I set a goal to become a Certified Expert in Biml. On February 1st 2016, I became the second BimlHero in the world – the first female and the first in Europe :)
It has been a long journey, and I have loved every part of it! Varigence announced the certification program in March 2015, and in April I decided to go for it. I was accepted into the program, and in June I attended 8 hours of advanced training. I spent the next six months working on my Biml solution. I was unable to work full-time on it due to other tasks and responsibilities at work, so I spent quite a lot of time on it at home as well. In November I completed the BimlHero Certified Expert Test, and in December I submitted my Biml solution and my case study of how I implemented the solution at work. In January Varigence reviewed my test, solution and case study, and in February I had the final code review with Scott Currie. It was very nerve-wracking having the creator of Biml go through my code! :) But I received lots of useful feedback and finally a congratulations on passing and becoming a BimlHero.
This is an achievement I’m truly proud of. I worked hard for many months and spent a lot of evenings and weekends learning and experimenting…
… well, ok, I wasn’t always 100% focused on my Biml solution when I worked during weekends :) …
…and now I’m really looking forward to continue sharing my knowledge and helping more Biml users. I’m very thankful and honored I got to speak at so many events in 2015, and I’m already looking forward to some great events in 2016 – including my first full-day Biml workshop with Scott Currie! I will also continue to blog about Biml, and let me tell you… Varigence has some super exciting stuff going on that I can’t wait to dig into and share with you guys :)