Made2Mentor

What are the best methods for generating Biml and what language should you use? Before I dive into this, let me begin with a disclaimer. I applaud anyone working with Biml to improve their productivity and eliminate needless, repetitive work. One of the primary advantages of Biml is its flexibility, which allows people to generate it many different ways to solve problems. I’m going to briefly discuss some concepts involved in the process and common ways I see it being generated. I’m also going to make recommendations that I’ve found work well in most circumstances. However, remember that this is only my opinion, and your mileage may vary. Let’s start by discussing two basic strategies for creating Biml, which I refer to as Scripting Biml vs. BimlScript.

Scripting Biml?

What exactly do I mean by “Scripting Biml?” Biml is simply an XML Language and therefore can be produced with many different tools and languages. When I talk about “Scripting Biml,” I’m referring to using some kind of tool or language to generate static Biml from outside of the “text” itself. This could take the form of XML language generation or simple string manipulation. I myself started down this route because string manipulation was familiar and comfortable to me. However, I quickly found that this method is very limiting and becomes unwieldy when attempting any complex scripting. I’ve listed several “Scripting Biml” examples, and included links for further research as well as a brief explanation of the methods/strategies being leveraged.

PowerShell

At PASS Summit 2013, Allen White (Blog/Twitter) presented Automate Your ETL Infrastructure with SSIS and PowerShell, which is available for streaming at that link. Allen demonstrates how to use PowerShell and Server Management Objects (SMO) to generate Biml. Be forewarned, unless you’re really into PowerShell, this video may not be for the faint of heart.

.Net Language – C#

One of my clients created an open source project called BimlGen, which is available on GitHub. BimlGen imports SQL Server database tables into Biml, and converts SQL Server Extended Properties into Biml Annotation Tags. Admittedly, it needs work, but it’s free and open sourced. I’m hoping that folks in the community will work on it to improve and extend its functionality. Simply enter the Server and Database Names, change the output folder if you like, and click Generate. The output folder will open and you should find a single Biml file which contains your Biml assets.

T-SQL

Another friend of mine, Garrett Edmondson (Blog), has a video on his blog where he uses T-SQL string concatenation to generate simple SSIS Packages.

BimlScript

BimlScript is created by embedding C# or VB.Net code within the Biml itself. Once you grasp the basics of BimlScript, which admittedly is not a trivial undertaking, your productivity will skyrocket. BimlScript is analogous to HTML, which is also a markup language, and ASP.Net. BimlScript allows the programmer to embed nuggets of C# or VB.Net code inside Biml to dynamically generate Biml assets, much the same way that ASP.Net dynamically generates web pages. These nuggets often include LINQ (Language Integrated Query) which is compatible with either C# or VB.Net. If all of this is new to you, don’t worry, I’ll explain all of this in more detail in a later post.

Should I use C# or VB.Net?

Let me preface this by saying that I started coding BASIC long ago on a TRS-80 machine, and I’ve been coding in BASIC ever since. However, when I started down the BimlScript path, I drank the C# Kool-Aid for the following reasons:

• The vast majority of the BimlScript samples available are written in C#. This fact alone was enough to get me to learn C#.
• C# with LINQ just seems cleaner once you get over C#’s affinity for semi-colons and curly braces. However, those C# curly braces tend to stand out better than VB statements embedded inside Biml.
• Mist doesn’t support Intellisense for VB.Net yet, but that functionality will be added soon.
• Arguably, C# programming skills are in more demand and worth more money than VB.Net.

That being said, if I had a team of skilled VB.Net programmers, I would not invest the resources to teach them C# just to work with BimlScript.

Types of Biml Scripts

There are three basic types of BimlScripts which support both C# and VB.Net.

1. Expandable – This is the most common type and is the only type supported by BidsHelper. These types of scripts are executed “manually” by the user.
2. Live – If you choose to purchase Mist, Biml Scripts can be automatically run in the background, primarily for convenience. One of the nice things about Mist is I can use the same script as either Expandable or Live at my choosing.
3. Transformers – These scripts change existing objects like SSIS Packages according to code logic and metadata. This is a Mist only option as well.

The vast majority of the scripts I share will be expandable and will use C#/LINQ. In the next post, I’ll explain exactly how I use metadata to control my BimlScript framework sample.

Simply put, metadata is data about data. Probably the most common way that SQL Server Professionals use metadata is when we query SQL Server System Views. In this article we’ll discuss using metadata to drive BimlScript automation. Before I start listing common metadata sources and methods, I need to briefly contrast two basic metadata philosophies I see being used. In some BimlScript frameworks, the metadata is highly detailed, as this typically allows the most flexible, configurable functionality. However, maintaining that detailed metadata can be tedious and time consuming. I subscribe to the philosophy of Convention over configuration. I prefer to code BimlScript in such a way as to expect that default values and behaviors (conventions) will be used and supplied. This leads to BimlScript that is much cleaner, easier to read, and maintain. At the same time, I try to provide mechanisms to allow as much flexibility as possible. For example, my BimlScript code follows default patterns I’ve identified and only deviates if specific metadata values are found.

There are several BimlScript metadata options/sources that I’ve noticed being used. Below I’ve listed the most common and I’ve included hyperlinks to blog posts or code samples where possible. I’ve noticed that more people are presenting on Biml than are blogging, but I’m not sure why.

SQL Server Metadata Tables

Simple Column Level Metadata Table

What you’ll often see in demonstrations are BimlScripts being controlled by metadata stored in dedicated SQL Server tables. Unfortunately, I couldn’t find any examples on the interwebs, so I provided the sample above. Information in the ColType field (abbreviated to fit on PowerPoint slides) indicates the functional role of said column. A column designated as BK would be treated as a Business Key, LKP triggers SSIS Lookup Transform code generation, etc. I’ve seen people use separate tables to house the metadata at the column level for Facts and Dimensions. I’ve also seen Table level metadata kept in one table and Column level metadata kept in a separate table. These metadata tables often look like Source to Target Mapping Documents. Creating and using SQL Server tables is second nature to most of us so this method is often used. These tables can be backed up with the rest of the database and also used to generate documentation for business purposes.

SQL Server Database Schema (DDL)

This takes several forms. For example, Samuel Vanga (Blog/Twitter) shows how to generate simple SSIS Packages dynamically using query results from the sys.tables Catalog View.

Some metadata strategies rely on Table and Column Names, though I couldn’t find examples on the web. For example, BimlScript can look for table names that begin with “Dim” or “Fact,” and take appropriate action. Also, I’ve seen people specify that any Column Name which ends in “Key” represents a Surrogate Key, or a column name that ends in “_BK” represents a Business (or Alternate) Key. I tend to avoid this practice because I’m a consultant and I work on many data warehouse projects. Therefore, I can’t dictate things like naming conventions. Also, I wouldn’t want business users to see column names like “CustomerCode_BK” on their reports.

SQL Server Extended Properties

Extended Properties have been around since SQL Server 2000 and people have been using them to document their database objects for some time. As a source for BimlScript metadata, Extended Properties have several advantages such as:

• They’re supported on most SQL Server objects.
• Can be automatically generated and maintained by third party software.
• Added and retrieved with relatively simple T-SQL code.
• They’re “hidden” from regular users.
• They’re backed up with the rest of the database.

I personally tend use SQL Server Extended Properties in conjunction with the next one, which is….

Biml Object Model – Annotation Tags

Like SQL Server Extended Properties, most (if not all) Biml objects such as tables, views, columns, etc. can be documented via annotations. There are several types of Annotations, in this article we’re going to focus on the “Tag” type. Below is sample of Biml code with Annotations that drive ETL automation.

 <Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Dimensions>
        <Dimension Name="DimCustomer" SchemaName="DataWarehouse.dbo" PackageSubpath="ImportTables">
            <Annotations>
                <Annotation Tag="TableRole">Dimension</Annotation>
                <Annotation Tag="LkpNoMatch">Unknown</Annotation>
                <Annotation Tag="IsScripted">Y</Annotation>
            </Annotations>
            <Columns>
                <Column Name="CustomerKey" ScdType="Key">
                    <Annotations>
                        <Annotation Tag="ColumnRole">SK</Annotation>
                        <Annotation Tag="IsViewColumn">Y</Annotation>
                    </Annotations>
                </Column>

Notice that I’ve specified a Dimension Table named DimCustomer which I’ve designated with a TableRole of Dimension. You’ll also notice that the first column, “CustomerKey” has a ColumnRole of “SK” which stands for Surrogate Key. These designations are only meaningful to the BimlScript you’re using. I’ll go into the nuts and bolts of how you refer to these annotation tags with BimlScript early next week.

What Metadata is Required?

As with so many other technical questions, the answer is “It Depends.” Do you lean more towards Configuration or Convention? As I mentioned above, I prefer Convention which should be obvious in the above snippet. Notice how any references to the source(s) seem to be missing? The BimlScript Framework I’ve recently demonstrated, which is available online, operates solely from the metadata in three table level annotations and three column level annotations. From that little bit of metadata, I “automatically” generate staging tables, overlaying views on the data warehouse including Role Playing Dimensions, Slowly Changing Dimension loads, Junk Dimension loads, and Fact table loads. Neat huh?

In the next article, I’ll take on the thorny topic of choosing a method and/or programming languages to generate Biml.