Skip to content

Tag: Biml

Business Intelligence Markup Language

Generating SELECT statements in Biml using GetColumnList

Biml logo representing the blog post "Generating SELECT statements in Biml using GetColumnList"

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:

p.FirstName, p.LastName

We will go through the different ways of customizing the output a little later in this post.

Continue reading →

Published: Last Updated: Categories: BimlTags: ,

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 →

Speaking at SQLSaturday Oslo 2018

SQLSaturday Oslo 2018In exactly one month from now, on September 1st, I will be speaking at SQLSaturday Oslo 2018! This year is a small milestone for us, as it is our fifth SQLSaturday in Oslo. Woohoo!

SQLSaturday Oslo 2018 Schedule

I will be presenting… Actually, hang on! I will get back to that in a bit. Scroll down for some rambling about my session :) But first, the important stuff:

There are 30 sessions by 27 speakers from 11 countries scheduled this year.

Of the 27 speakers, there are 7 women, and 4 of the women are Norwegian! These are amazing numbers for an event like this. I am SO proud and SO happy :D Thank you to the organizers for encouraging and supporting women in tech, and a huge thank you to these amazing women for being great role models: Dagrun Haugland, Gabi Münster, Ida Bergum, Kathi Kellenberger, Sara Jønvik, and Steph Locke.

There are also 3 Norwegian men speaking! Very happy to see Helge Rege Gårdsvoll, Martin Keseler Barland, and Antonio Abalos Castillo back at SQLSaturday Oslo. That makes it a total of 7 Norwegian speakers this year. Woohoo!

Finally, there are 4 precons scheduled: You can learn about Azure SQL Data Warehouse from Simon Whiteley and Terry McCann, SQL Server Tools for Query Tuning from Grant Fritchey, PowerShell and SQL Server from Rob Sewell and Drew Furguiele, or R for Everyone from Steph Locke.

You definitely don’t want to miss this event. Register now! And follow @SQLSatOslo and #SQLSatOslo on Twitter to join the conversation. Hope to see you in September! :)

From SQLSaturday Oslo 2014 to SQLSaturday Oslo 2018

Ok, now that we have the important parts out of the way, let’s return to my session. Did you know that the very first technical session I ever presented was at the first SQLSaturday Oslo in 2014? I presented a session called Generate SSIS packages automatically with Biml and BimlScript. This year, I will be presenting my updated Biml for Beginners: Script and Automate SSIS Development session.

Same topic, same basic concept and ideas, some similar visualizations, but a very different session. And I mean in more ways than going from 4:3 to 16:9:

I have changed

SQLSaturday Oslo 2014 - Cathrine Wilhelmsen - Slide 1 SQLSaturday Oslo 2018 - Cathrine Wilhelmsen - Slide 1
My hair is much longer and my URL is much shorter. I’ve become a BimlHero and Data Platform MVP, and I’ve co-authored two Biml books. And it all started with this one Biml session back in 2014! Crazy :)

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 →

Published: Last Updated: Categories: BimlTags: ,