Skip to content

Generating SQL using Biml (T-SQL Tuesday #110)

T-SQL Tuesday #110: Generating SQL using Biml

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.

Continue reading →

Don’t Repeat Your Biml – C# Extension Methods

Don't Repeat Your Biml - C# Extension Methods

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.

Continue reading →

Biml for OData Source and Connection Manager in SSIS

Biml for OData Source and Connection Manager

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? :)

  1. Install or upgrade to BimlExpress 2018
  2. Create an example SSIS package using an OData Source and Connection Manager
  3. Convert the SSIS package to Biml
  4. Done! :)
Convert SSIS Packages 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.

However!

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.

Ok, let’s dig into the actual Biml code!

Continue reading →

Biml Annotations and ObjectTags

Biml Annotations and ObjectTags

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.

Continue reading →