Skip to content

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.

How to generate a SELECT statement using GetColumnList

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) { #>
    SELECT <#=table.GetColumnList()#>
    FROM <#=table.SchemaQualifiedName#>;
<# } #>

In BimlExpress, it looks something like this:

Screenshot of using GetColumnList to generate SELECT statement in BimlExpress

How do you use these SELECT statements?

The example above is just that: an example to show the usage and output of GetColumnList :) By itself, this does absolutely nothing. But! You can use the output in a different application, or wrap the Biml code in more useful Biml code.

For example, you can copy the output from the BimlExpress preview pane into an application like SQL Server Management Studio (SSMS) or Azure Data Studio (ADS) and run the statements there.

A more common scenario is to use GetColumnList in a data flow source transformation (like OleDbSource) when generating SSIS packages from Biml. In short, this can be used anywhere you select from a table in your solution.

Customizing GetColumnList code fragments by passing parameters

You can filter the columns and customize the output of GetColumnList by passing in different parameters. In the table below, you will find the six different ways of using GetColumnList:

MethodExample UsageExample Output
GetColumnList()GetColumnList()[PersonID], [FirstName], [LastName]
GetColumnList(tableAlias)GetColumnList("p")[p].[PersonID], [p].[FirstName], [p].[LastName]
GetColumnList(predicate)GetColumnList(c => c.IsUsedInPrimaryKey)[PersonID]
GetColumnList(predicate, tableAlias)GetColumnList(c => c.IsUsedInPrimaryKey, "p")[p].[PersonID]
GetColumnList(tableAlias, beginningDelimiter, endingDelimiter)GetColumnList("p", "", "")p.PersonID, p.FirstName, p.LastName
GetColumnList(predicate, tableAlias, beginningDelimiter, endingDelimiter)GetColumnList(c => c.IsUsedInPrimaryKey, "p", "", "")p.PersonID

In total, you can use four different parameters. Three parameters are simple string values: tableAlias, beginningDelimiter, and endingDelimiter. The fourth parameter, predicate, is a little more complex.

What is the predicate parameter?

In Biml column methods, the predicate parameter is a lambda expression. Ok. But what is a lambda expression? You can read the full, proper explanation in the C# programming guide. I prefer to explain with a simplified example :)

The code below is a lambda expression. The arrow => is the lambda operator. To the left is the input parameter. To the right is the expression we want to evaluate:

column => column.Name == "PersonID"

In short, when we call GetColumnList on a table and pass in the predicate parameter, we loop over all the columns in that table. For each column, we evaluate the expression. If the expression is true, we include the column in the output. If the expression is false, we exclude the column. In the example above, we will only include the column named PersonID in the output.

Inside the lambda expression, we can name the input parameter anything. In the example above, I use “column for readability. In my actual code, I often shorten it to “c” to save a few keystrokes:

c => c.Name == "PersonID"

The actual expression can check any property that belongs to the input parameter. For example, for columns, we can check the name, data type, if it is nullable, if it is part of a key, and so on:

c => c.Name == "PersonID"
c => c.DataType != System.Data.DbType.Guid
c => c.IsNullable
c => !c.IsUsedInKey

Let’s take a look at a few more examples!

Example: SELECT with table alias

In this example, we pass in the tableAlias parameter “src”. We also manually alias the table as src:

SELECT <#=table.GetColumnList("src")#>
FROM <#=table.SchemaQualifiedName#> AS src;

In BimlExpress, it looks something like this:

Screenshot of creating SELECT with table alias

Example: SELECT specific columns

In this example, we pass in the predicate parameter c => c.IsUsedInPrimaryKey:

SELECT <#=table.GetColumnList(c => c.IsUsedInPrimaryKey)#>
FROM <#=table.SchemaQualifiedName#>;

In BimlExpress, it looks something like this. Note the lack of columns where the table does not have a primary key:

Screenshot of creating SELECT with specific columns

Example: SELECT specific columns with table alias but no delimiters

In this example, we pass in the predicate parameter c => !c.IsUsedInPrimaryKey, the tableAlias parameter “src”, the beginningDelimiter parameter “”, and the endingDelimiter parameter “”:

SELECT <#=table.GetColumnList(c => !c.IsUsedInPrimaryKey, "src", "", "")#>
FROM <#=table.SchemaQualifiedName#>;

In BimlExpress, it looks something like this. Note the lack of primary key columns and lack of square bracket delimiters:

Screenshot of creating SELECT with specific columns, table alias, but no delimiters

Summary

You can generate custom SELECT statements in Biml by using the column method GetColumnList. In addition, by passing in one or more parameters, you can filter the columns and customize the output. This is a very powerful and flexible feature, especially when using predicates.

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

Published: Last Updated: Categories: BimlTags: ,

About the Author

Cathrine Wilhelmsen is a Microsoft Data Platform MVP, BimlHero Certified Expert, international speaker, author, blogger, and chronic volunteer. She loves data and coding, as well as teaching and sharing knowledge - oh, and sci-fi, chocolate, coffee, and cats :)

Comments

Hi! This is Cathrine. Thank you so much for visiting my blog. I'd love to hear your thoughts, but please keep in mind that I'm not technical support for any products mentioned in this post :) Off-topic questions, comments and discussions may be moderated. Be kind to each other. Thanks!

Hi Cathrine. Great hint/tip that I’m going to use more often to help me generate my queries. But, I want more. Is there a function or feature that can be used to produce the column or columns used in a table’s primary key? That would be very nice in helping to generate sorts and joins, both in t-sql and SSIS development.

Thanks,
Luther

Hi Luther, you may want to read through my post one more time. Several of my examples already show how to find the column(s) used in a table’s primary key :)

I was apparently too excited to read the rest of the article and proceeded to completely skip everything after using the first part to generate my queries. Thanks!

Happy to hear it was useful! :)

Will the GetColumnList get the fields on a flat file source to pass to the next destination as well?

Hi Catherine!
Thank you so very much! Your posts have been tremendously helpful to me. I am just starting to learn BIML

Is there a way to exclude the Primary Key column on the <ExternalTableOutput Table="” /> tag?
My target tables need the Primary Identity keys. Those keys don’t accept VALUES on the INSERT. But, ExternalTableOutput pulls in the PK for the target table.

I’d like to use ExternalTableOutput (or some other way) to INSERT to my target tables and exclude the PK columns.

I suppose that I cannot use this: !c.IsUsedInPrimaryKey)
I could not make that work.

If you could point me to one of your blog posts that explain the how to do this, I would be most grateful!

Thank you.
paul

Hi Paul, you need to approach this from how you would build the SSIS package manually. In the destination, you wouldn’t exclude or remove the primary key column, you would simply set the mapping to ignore the primary key column. This is also the default behavior in Biml when there are no incoming columns with the same name as the destination column. As long as your source doesn’t have a column with the same name as the destination primary key column, and you don’t create a derived column with the same name as the destination primary key column, it should work without you having to change anything.

Hi Cathrine, One of the column name in a table is [Customer’s address]. Check BIML for errors says that it’s unable to execute the query on the source. The same query executes fine on Sql Server Management Studio.

The error message is Incorrect syntax near ‘s’.
Unclosed quotation mark after the character string ”.

Hi! This is Cathrine (again). Just a reminder. I'd love to hear your thoughts, but please keep in mind that I'm not technical support for any products mentioned in this post :) Off-topic questions, comments and discussions may be moderated. Be kind to each other. Thanks!

Share Your Thoughts?