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
Do you use Visual Studio Code? If so, I have some cool news for you! The Biml Support extension was released on July 19th, 2019. Once you install it, you can view your Biml files in Visual Studio Code with Biml syntax highlighting. Woohoo!
To install the extension, search for Biml Support (or even just Biml) under Extensions, then click Install. That’s it! :)
Biml Syntax Highlighting
Please note that you only get syntax highlighting with this extension. You do not get the full Biml or .NET intellisense, the BimlScript preview pane, or the ability to generate SSIS packages from Biml. For those things, you will still need BimlExpress for Visual Studio.
However! If you simply want to view your Biml files in a lightweight editor, the Biml Support extension works beautifully :)
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.