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

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.

What is Biml?

Biml, Business Intelligence Markup Language, is a programming language that can generate SQL scripts, SSIS packages, SSAS Cubes, Tabular Models, Azure Data Factories and more from your source metadata. There are two ways to generate these objects. You can either use a free add-in to Visual Studio called BimlExpress, or use a separate licensed application called BimlStudio.

Since you are limited to only generating SSIS packages in the free BimlExpress add-in, Biml is mostly known for being a tool for automating SSIS development.

The Biml language itself consists of two parts. The first part is what I call flat Biml, which is just plain XML. This is where you describe the actual objects you want to generate, for example SSIS packages:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <Package Name="EmptySSISPackage">
      ...
    </Package>
  </Packages>
</Biml>

The second part is BimlScript, which are code blocks that contain C# or VB. You can easily spot these code blocks because they start and end with <# … #> and have a bright yellow background:

<# var DemoVariable = "Hello World!" #>

By using BimlScript code blocks, you can generate, control and manipulate flat Biml. For example, you can import metadata from a source database, loop over all its tables, and generate Biml code to create an SSIS package for each table.

In short, you write BimlScript (C# or VB code) to generate Biml (plain XML) that can generate SSIS packages.

Wait. My brain hurts.

I know. That was a lot of text to basically explain this:

First, you write some BimlScript code, for example to loop over all your tables. Then, the BimlScript code will generate the flat Biml, for example one definition for each table. Finally, you can generate the actual SSIS packages from the flat Biml:

Inside of BimlExpress, it looks something like this. You write your BimlScript in the top pane, and then all the code blocks get parsed and expanded automagically. You can see the results, the flat Biml, in the preview pane on the bottom:

Now, here’s the secret: You don’t have to generate any SSIS packages. In fact, you don’t have to write any Biml at all!

Huh? Biml without Biml?

YES! Is your brain hurting yet? 😁 Ok, so technically, you are writing BimlScript without Biml. You can write BimlScript to output SQL. Or to output any kind of text, for that matter. All you are doing is taking advantage of how the BimlExpress preview pane parses BimlScript code blocks and displays the results in the preview pane.

That means that we can go from using BimlExpress to display flat Biml in the preview pane:

To using BimlExpress to display any kind of text in the preview pane:

So we can do silly things like this:

But… why not just use <Something Else>?

Believe me, I hear you. You can use dynamic T-SQL to do the same thing. Or PowerShell. Excel macros, even. And probably fifteen other . Heck, this is basically T4 Text Templating, so why not just use that?

Because Biml is fun!

Ok, I think Biml is fun. And it’s my party and I’ll cry if I want to my blog and I’ll write about it if I want to 😁

On a serious note, though, the main reason I use Biml to generate SQL is that I also use Biml to generate SSIS packages. It makes sense for me to stick to one tool and one language for my entire Data Warehouse automation process. In one Biml project, I can automate all the SQL scripts and SSIS packages I need with a few clicks. And that is powerful.

Ok. That turned out to be a little bit more than just a little bit of background of Biml 😅 Let’s look at some actual examples.

How to generate SQL using Biml

The simplest way of generating SQL using Biml is to use one of the built-in table methods like GetDropAndCreateDdl or GetSelectSql.

First, we create a connection to our source database (replace “Data Source=…;” with your actual connection string) and import all its metadata by calling GetDatabaseSchema. Then, we output a SELECT statement for each table in the database:

<# 
    var sourceConnection = SchemaManager.CreateConnectionNode("Source", "Data Source=...;");
    var sourceMetadata = sourceConnection.GetDatabaseSchema();
#>

<# foreach (var table in sourceMetadata.TableNodes) { #>
    <#=table.GetSelectSql()#>
<# } #>

In BimlExpress, it looks something like this:

Now imagine if you had 500 source tables and you needed to create a view for each of those tables in just hours. How would you solve that? I would solve it using Biml:

<# 
  var sourceConnection = SchemaManager.CreateConnectionNode("Source", @"Data Source=...;");
  var sourceMetadata = sourceConnection.GetDatabaseSchema();
#>

<# foreach (var table in sourceMetadata.TableNodes) { #>
CREATE VIEW dbo.<#=table.Schema.Name#>_<#=table.Name#> AS
  <#=table.GetSelectSql()#>
GO
<# } #>

Tadaaa! In just 10 lines of code, I can create 2, 5, 100 or 1000 views from my source metadata in just seconds. (Ok, maybe minutes, if I count the time it takes to write all the code and execute the script 😁 )

True story!

This actually happened to me in a previous job. We had a fairly complex ETL solution for the most critical part of our Data Warehouse. Many SSIS packages, views, and stored procedures queried the tables that were replicas of the source system tables. One day, we found out that the source system would be deploying a new version of their database the following day. In every single table, some columns were removed, others added, and many changed data types.

Oh.

There was no way that we could manually update all our SSIS packages, views, and stored procedures in less than a day. Thousands of users depended on our solution. It was too late to pause the source system changes.

Oh.

After some panicked brainstorming, we came up with the idea of using views. We used Biml to create views that looked exactly like the original tables, gave them the original table names, but pointed them to the new tables. By doing this, our existing Data Warehouse solution continued to function without us having to make any changes other than deploying the new views and handling the new tables.

PHEW!

Summary

This month’s T-SQL Tuesday topic was “Automate All the Things”. Garry wanted to know what this phrase means to each of us. What do we want to automate? What is our go-to technology for automation?

For me, the answer is Biml 😊 Biml has saved my bacon countless times, and also saved me hours and days and weeks of development time. It allows me to automate both SQL scripts and SSIS package development, which in turn reduces the risk of manual errors.

In later blog posts, I will go through how to generate custom SELECT, JOIN, UPDATE, and MERGE statements. If you found this interesting, stay tuned! 🤓

About the Author

Cathrine Wilhelmsen is a Microsoft Data Platform MVP, international speaker, author, blogger, organizer, and chronic volunteer. She loves data and coding, as well as teaching and sharing knowledge - oh, and sci-fi, gaming, coffee and chocolate 🤓