Strategies and Languages for Generating Biml

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.


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. BimlGen_screen


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 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.

Biml Metadata Options and Considerations

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

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="">
        <Dimension Name="DimCustomer" SchemaName="DataWarehouse.dbo" PackageSubpath="ImportTables">
                <Annotation Tag="TableRole">Dimension</Annotation>
                <Annotation Tag="LkpNoMatch">Unknown</Annotation>
                <Annotation Tag="IsScripted">Y</Annotation>
                <Column Name="CustomerKey" ScdType="Key">
                        <Annotation Tag="ColumnRole">SK</Annotation>
                        <Annotation Tag="IsViewColumn">Y</Annotation>

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.

Agile Data Warehouse Development using Biml

Agile development is good, Waterfall is bad. We hear this all the time, along with un-documented statistics claiming that approximately 80% of Data Warehouse projects fail. So, we as Business Intelligence Professionals are told that we must be Agile. Whenever I taught Dimensional Modeling and Data Warehouse classes, I could count on at least one student asking me how to apply Agile principles to this process. My answer, up until several months ago, was that true Agile Data Warehouse development was a pipe dream. The reason why is fairly obvious if you follow the Kimball method. In a nutshell, when building Star or Snowflake Schemas, one should design, build, and load related Dimensions before Fact Tables. Since each Fact Table, which contains your metrics, typically relates to a majority of the Dimensions, the first Fact Table load requires a great deal of time from the Data Warehouse Development Team and business stakeholders. Dimension tables require a lot more time and resources to develop than Facts. After that first “wave” if you’ll pardon the pun, additional Fact tables and metrics can be delivered quite rapidly. The natural consequence of this is a form of Waterfall development.

We’ve always tried to ameliorate this problem by prioritizing business processes by their “Business Value” and feasibility. In other words, we typically chose to create our first Fact Table(s) for business processes that were worth a lot of money and were the easiest to build and load. We traded a true Waterfall, delivering the entire solution at the end, for a partial Waterfall. Less painful? Sure, but that doesn’t equate to Agile development.

Agile Data Warehouse Design

Several months ago Tim Costello (Blog/Twitter), a good friend of mine from the community, urged me to read the following book:

Buy This Book

Buy This Book

Agile Data Warehouse Design: Collaborative Dimensional Modeling, from Whiteboard to Star Schema

I have a great deal of respect for Tim, so I read it and the book is excellent. It offers comprehensive tools and techniques for improving the entire process of designing a data warehouse. The book emphasizes quick iterations, each of which involves:

  1. ModelStorming – Their new method of conducting business process interviews.
  2. JIT (Just In Time) Dimensional Modeling
  3. ETL Development
  4. Visual BI Delivery
  5. Revisions

These ideas and concepts are great and they address several issues with the traditional Data Warehouse Lifecycle. However, up until recently it was impossible to build a data warehouse this way.

Agile Data Warehouse Design Development?

In an ideal world, building a data warehouse would be an Agile process. The first sprint would deliver one or more Fact Tables, and possibly a couple of prototype or primitive Dimensions. In each successive sprint, more Fact Tables could be deployed, new Dimension tables could be added, and existing Dimensions could have additional attributes created. Existing tables would easily “adapt” to the changes. For example, existing Fact Tables would automatically be related to new Dimension tables, if appropriate. Ideally, this whole process would be fluid and quick. You know umm… Agile. However, anyone who develops ETL processes knows this is not the case.

Let’s consider the work involved in making just one small minor change such as adding a new attribute to an already existing Dimension. The process may involve the following steps.

  1. Alter the Dimensional Model if using a dedicated tool.
  2. Alter the Source Query(ies).
  3. Any dependent objects need to be altered as well such as staging tables, stored procedures, user views, etc.
  4. T-SQL must be written or generated to make these changes.
  5. The SSIS Package(s) needs to be edited.
  6. If SSAS Cubes are being used they also need to be edited.

In traditional ETL development, none of these objects above react dynamically to the change, they all have to be handled separately. This is only scratching the surface. Best practices suggest using Source Control on all of the objects above, which is very time consuming. Also all of these changes should be fully tested in a separate environment before being deployed to production. So, this single small incremental improvement could require several days (perhaps weeks) of development work. So, why did I preface this with section with “until recently?”

Biml Facilitates Agile Data Warehousing

“BimlScript enables developers to build and load a Data Warehouse nearly as fast as they can draw the boxes and lines.”

Using a quality BimlScript framework eliminates a lot of the pain, tedium, and costs involved in Data Warehouse Development and ETL. How would the same change from above be accomplished using a good BimlScript framework?

  1. Alter the Dimensional Model if using a dedicated tool.
  2. Alter the Source Query(ies).
  3. Automatically import the changes into Biml.
  4. Automatically regenerate every single object listed above including staging tables, user views, SSIS Packages, SSAS Cubes, etc.

Well, what about Source Control? If the BimlScript framework automatically generates all of those objects above from the Dimensional Model and its metadata, what really needs to be checked into Source Control? The Dimensional Model and the BimlScripts. Since the only object that was altered during this “sprint” was the Dimensional Model itself, that’s the only object which needs to be checked in and out of Source Control.

What about testing? If the results of your BimlScripts have been tested in prior sprints, and you didn’t change any of that code, how much QA testing is really required? I would argue that only a cursory amount of testing would be required to validate your metadata.

In short, BimlScript is the perfect companion to the Agile design methods proposed in that book. Stay tuned to the blog for more details.

Biml Syntax Basics and Rules

Biml is a human readable XML language, created by a company called Varigence, which compiles into SQL Server objects such as tables, views, SSIS packages, and SSAS cubes. In this post, we’ll cover the basics of the Biml language so you can start coding as fast as possible. Since, I’ve always found that it’s easier to learn new things by example, I will use one of the scripts from my Biml – The Next Step in Agile Data Warehousing presentation.

Biml is not proprietary and anyone can create software which generates and/or consumes it. There are many ways to generate Biml, but most people currently use one of two methods. For those who don’t want to pay for a dedicated product, Biml (and BimlScript) can be written in the Business Intelligence Development Studio (BIDS) or SQL Server Data Tools (SSDT) and compiled with a free program called BidsHelper. Most ETL Developers have at least heard of BidsHelper, and I’ve been using it for years. Instructions on installing BidsHelper and creating your first simple Biml file can be found here.

The other popular method is to use Mist, which is a product of Varigence. Let me preface what I’m about to say with a disclaimer.

I do not work for Varigence and am not financially compensated by them. I don’t benefit in any way if you purchase Mist or choose to use BidsHelper instead.

That being said, I find Mist to be invaluable for serious BimlScript development. Most of my scripts are created using Mist, but they can all be edited, compiled, and executed with BidsHelper. In the future, if I provide a script which cannot be run without Mist, I will clearly point that out. The basic development process when using BidsHelper varies from Mist in several ways. One way is file naming and grouping. If using Mist to create or import tables, connections, and other objects, a separate file is usually created for each. In Mist, BimlScripts can be manually executed or they can be “live.” If they are live, the objects are sort of created virtually and updated in real time. Since Mist keeps track of these live objects, it doesn’t matter if each Connection, Database, Schema, etc. is in a separate file.

In BidsHelper, we tend to group similar Biml objects together because there is no live option when using that program. Therefore, any files containing objects must be pre-selected before execution. So, if your connections, databases, and schemas were spread across 8 files, you’d have to constantly pre-select them whenever you ran other BimlScripts. Obviously, the fewer Biml files we need to highlight, the faster we can work. To that end, I and others typically start by creating a standard file called Environment.biml. This file contains my Connections, Databases, and Schemas.

<Biml xmlns="">
	<!--See More BimlScripts at -->
        <OleDbConnection Name="connDW" ConnectionString="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DataWarehouse;Data Source=DevServer" />
        <OleDbConnection Name="connSource" ConnectionString="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=m2m601c61;Data Source=DevServer" />
        <Database Name="DataWarehouse" ConnectionName="connDW" />
        <Database Name="M2MSource" ConnectionName="connSource" />
        <Schema Name="dbo" DatabaseName="M2MSource" />
        <Schema Name="dbo" DatabaseName="DataWarehouse" />
        <Schema Name="interim" DatabaseName="DataWarehouse" />
        <Schema Name="star" DatabaseName="DataWarehouse" />

Note, that while I use the same Environment file in both BidsHelper and Mist, when using Mist the Project View shows one file, while the Logical View properly segregates them.



Root Element

Let’s break down the basic parts and rules of a Biml XML file. The first line is called the Root element and references the XSD file. This is the XML Schema Definition file which contains the rules of the Biml declarative XML language. Under the root elements are plural collections of elements/objects such as Connections, Databases, Schemas, Packages, etc. Each plural collection will have one or more individual objects nested inside, which you can see in the above script. Also, each plural collection and each nested Biml object must have a closing tag.

Object Attributes/Properties

Each type of Biml object has different properties that pertain to it, only some of which are required. In my example, you’ll notice that I’ve defined two databases, each of which has two sources, along with several schemas. One of the schemas I’ve defined is named “star.” That’s a little inside joke because I recently worked on a project where all of their dimension and fact tables were placed inside a “star” schema.

All Biml objects including the plural collections are case sensitive. Varigence uses Pascal Case in which words are concatenated and each new word begins with a capital letter, like OleDbConnection. Biml attribute values (properties) must be placed inside quotes and are case sensitive as well. Therefore, a schema named “Star” is not the same as one named “star”.

Biml Comment Syntax

Biml Comments are created using the syntax I’ve show above. Keep in mind that XML Comments can span more than one line. The following is perfectly valid.

<Biml xmlns="">
	<!--See More 
	at  -->    

Object Naming and Scope

Notice the way that objects are named in the above example. Obviously schema names cannot be completely unique otherwise you could only have one database in your Biml project with the schema “dbo.” However, objects must be unique within their scope. In this case, my schema names must be unique within the Database scope.

Illegal XML Characters and the use of CDATA

Remember that Biml is simply XML and as such has certain characters that have special meaning are considered illegal. Some common characters which can cause trouble are the comparison characters which are often part of T-SQL or SSIS Expression statements. If you’d like you can replace the following characters with their corresponding pre-defined Entity Reference:
illegal chars

For example, an Execute SQL Task with a Direct Input query like the following will result in an error.

Select  Fields From  SomeTable Where SomeField < 5

This can be re-written as:

Select  Fields From  SomeTable Where SomeField &lt; 5

However, I find that this makes code difficult to read and follow, so I typically enclose this kind of code inside a CDATA section which tells XML not to parse it. CDATA can span multiple lines as well. Since most of my Biml is generated, including the CDATA section is trivial.

<![CDATA[ Select  Fields From  SomeTable Where SomeField < 5 ]]>  

White-space Issues

White-space can be inserted in your code to make your code easier to read and the compiler ignores it. However, white-space is NOT ignored in your property values. Consider part of the script above.

<Biml xmlns="">
        <Database Name="DataWarehouse" ConnectionName="connDW" />

I can add extra spaces or carriage returns to the Biml objects themselves or between properties and the compiler ignores them. So, the following would work:

<Biml xmlns="">
    <Databases       >
        <Database Name="DataWarehouse"        ConnectionName="connDW" />


I realize that the code I just posted is hard to read, but you get the point. Conversely, white-space is NOT ignored inside of proprty values. So, the following will throw an error:

<Biml xmlns="">
        <Database Name="DataWarehouse" ConnectionName="connDW      " />

Could not resolve reference to ‘connDW ‘ of type ‘DBConnection’. ‘ConnectionName=”connDW “‘ is invalid.

What’s Next?

Stay tuned because in upcoming articles we’ll cover the following:

  • A tutorial on BimlScript syntax and how to create them.
  • Additional Code Samples from my Biml – The Next Step in Agile Data Warehousing presentation, along with an explanation of how each code works.
  • Everything you need to know about Biml datatypes.

Any questions?

Announcing Two Biml Presentations Next Week

My enthusiasm for Biml must be catching because I’ve been asked to give two presentations next week.

I’ll be giving a brand new presentation on Tuesday, November 12th, at 4:30pm for the MSBIC group at the Microsoft Building in Irving, TX. This presentation is targeted towards Business Analysts, management, and other members of the data warehouse team. If your boss has been on the fence about adopting Biml, try to get him/her to see this presentation. Anyway, details can be found on the MSBIC site and the abstract is as follows:

The Business Case For Biml

Business Intelligence Markup Language is literally changing the way we think about Data Warehouse Design and ETL Development, but what impact will this have on the rest of the business? In this presentation we’ll discuss how Biml adoption may affect things like the Data Warehouse Development Lifecycle, Team Dynamics and Roles, Quality Assurance Testing, and Business Process Interviews. We will also discuss how to maximize your return on investment (ROI) by utilizing BimlScript in your data warehouse projects.

Next Thursday, November 14th, at 6pm CT I’ll be giving a remote presentation to the Tulsa SQL Server User Group. I’m going to present a re-factored and more detailed version of the Biml presentation I gave at SQL Saturday #255 last week. I’m hoping that it will be available for live streaming and I’ll follow up with more details once everything is straightened out. I’m thrilled to be giving this presentation again because I will have more time and can show more of the code. Once again, the abstract follows.

BIML – The Next Step in Agile Data Warehousing

Business Intelligence Markup Language (BIML) is literally changing the way we think about Data Warehouse design and ETL. In this interactive demo-focused presentation you’ll see how you can rapidly iterate through the Data Warehouse Development Cycle and provide ROI almost immediately. Learn how you can use BIML Script to automatically create staging tables, SSIS Packages, and other necessary objects during the modeling process.

But wait, there’s more….

I mentioned my intention to present a full day Biml Workshop in a previous article, and a local firm has approached me and would like to sponsor it. I know a lot of my NTSSUG SQL Family are really eager for some Biml training and I promise to post any developments and updates right here on the blog.

Hope to “see” some of you at the presentations.

Thank You to My NTSSUG SQL Family

Dave Ryan Vic
I’m happy to announce that I’ve just been elected to the North Texas SQL Server User Group Board of Directors. I owe so much to the PASS Community in general, but even more to our local group. In case you aren’t aware, the NTSSUG is one of the most active and successful groups in the entire country, and I’m very honored to join the board.

I want to just take a moment to acknowledge what our group has already achieved.

  • We’ve had five successful SQL Saturday events. Our last, SQL Saturday #255 had more than 500 attendees.
  • Hosted SQL Rally in Dallas.
  • We routinely draw 60 to 80 participants to our monthly meetings.

I’d like to take a moment to thank the two people who also ran for the board this round, Miyaka Fusi (Twitter) and Chris Utter. Being a board member is a serious commitment, and I hope to continue to see you folks at meetings and events.

Also, I’d like to take a moment to thank our outgoing two board members as well. Erin Welker (Blog/Twitter) will be retiring as our Past President and she’s served since the group’s inception more than 10 years ago. Tim Mitchell (Blog/Twitter) has been our Director of Finance for the past four years and his contributions have been vital to the growth of the group.

The current board is a very cohesive team made up of talented and dedicated professionals. Great guys one and all.

  1. Sri Sridharan (Blog/Twitter) – President
  2. Ryan Adams (Blog/Twitter) – Director of Programs
  3. Ganesh Gopalakrishnan (Twitter) – Director of Logistics and Communication

I officially become a board member on January 1st, and I promise to do my very best to measure up to the standards set by my fellow board members as well as those that served before me.

Wish me luck!

Biml Makes You THE ONE

The Matrix is one of my favorite movies. The story, special affects, and the theme are so compelling that I’ve watched the movie at least a dozen times. I referenced it in the introduction for my SQL Saturday 255 presentation, Biml – The Next Step in Agile Data Warehousing, and it fits in perfectly with my ongoing series, Why I Choose Biml Scripting as well. How does Biml relate to the Matrix?

The Story

What is the Matrix really about? It’s about this really smart guy, who’s disillusioned with his surroundings and life. Everything seems flat and boring, and he can sense that there’s something fundamental being hidden from him. He goes to work everyday, doing the same things, but is spending his nights on his computer searching for the secrets of The Matrix.
This is a person who seeks more. He seeks to gain a true understanding of his environment, his role in it, and eventually to gain mastery of it and his fate. After enduring the process of searching for a guide, learning what the Matrix is, and lots of Kung Fu and gun battles, he has an epiphany.

Neo Sees the Source Code

Neo is The One

Neo is The One

He realizes that he is in fact “The One.” He can see the underlying code of The Matrix and can use his gift to make his world into what he wants it to be. He doesn’t have to toil in tedium for the rest of his life.

Can I Learn to Dodge Bullets?

Since Biml compiles into SQL Server Objects like views, tables, SSIS packages, and SSAS cubes, you can learn to automate their creation and functionality with BimlScript. Essentially, you gain the ability to “see the code,” and all of the resulting power that conveys. As I demonstrated on Saturday, you can create BimlScripts which will automatically create and load a data warehouse based upon metadata.
Can you learn to dodge or stop bullets? Well, no. However, if you spend time mastering BimlScript, you can build and load a data warehouse nearly as fast as you can draw the boxes and lines. You can save hundreds of hours of tedium, deliver data warehouses in rapid iterations, and save companies untold thousands of dollars.

Will You Take the Blue or Red Pill?

So, will you take the Blue pill and go on about your everyday life? Are you content to build and repeatedly edit the same old SSIS packages again and again? Or will you take the Red Pill and learn BimlScript instead? Will you learn how to automate your data warehouse loads, create new SSIS Packages, SSAS Cubes, and other SQL Server objects at will? The choice is yours.

As I closed my Biml presentation, I mentioned that I would be posting the BimlScripts I wrote for it on the blog for download. I’m also going to include articles detailing how the code works and how to write similar code yourself. Stay tuned in the coming days and weeks for those articles. Also, I am working on scheduling some full-day BimlScript Workshops. I’m currently trying to find a good venue in Dallas/Fort Worth, and may have already secured one in Austin, TX. Finding the right venues will be critical as I’d like to make the training as affordable as possible. If you would like to attend a workshop, know of a good venue, etc. feel free to contact me at

Continue to stay tuned to the blog. I’ll be posting more Biml articles, announcing workshops, and other cool stuff in the coming weeks.

Upcoming Presentations at NTSSUG and SQL Saturday Dallas

Just wanted to give my readers, both of you, a quick heads up on my upcoming presentations, both of which deal with BimlScripting.

“Biml Basics” Tuesday Night at NTSSUG

Tuesday night (10/29) I’m presenting the final session (for now) of my Data Warehousing Basics and Beyond series for the North Texas SQL Server User Group. We’ve covered the basics on how to create both Dimension and Fact Tables, as well as how to load them with SSIS. This final presentation will be on Biml Basics. We’ll discuss what Biml is, the basics of how to extend it with script, and why you should adopt it. I think everyone will find it interesting and this session will serve as a good pre-lude to my upcoming presentation at SQL Saturday 255.

SQL Saturday #255 – Arlington, TX

Our next SQL Saturday is this week (11/2/2013) and I’ll be volunteering as usual. The regular event is free to everyone and we still have room for more attendees, so I urge everyone in the area to attend. I’ll be presenting the following at the event:

Biml – The Next Step in Agile Data Warehousing

Business Intelligence Markup Language is literally changing the way we think about Data Warehouse design and ETL. In this interactive, demo focused presentation you’ll see how you can rapidly iterate through the Data Warehouse Development Cycle and provide ROI almost immediately. Learn how you can use Biml Script to automatically create staging tables, SSIS Packages, and other necessary objects during the modeling process.

Also, we are offering three pre-conference sessions on Friday (11/1) from well known experts in their field, which you umm… may have heard of. 🙂 They are an excellent value at $120 for a full day of training. The speakers and session titles are as follows:

A Day of SSIS 2012 – Andy Leonard
Query Perfomance Tuning – Grant Fritchey
Data Science Crash Course- Drew Minkin

More details for the SQL Saturday event and Precons can be found here. I hope to see a lot of you there!

Biml - Because I'm a Cheater

I’ll admit it, I’m a cheater. Now, before you click away in disgust, give me a minute to explain. Almost 15 years ago, when I felt stuck in my career as “The Made2Manage Guy,” I’d routinely run out of things to do. I mean, once you’ve learned all the pitfalls and gained a mastery of the program and job, what more was there to do? I can almost hear you saying, “Umm… you could learn something else and grow.” Well I hear ya, and nowadays I can’t imagine wasting idle time like that. However, this was a long time ago, and like so many people in the early days of the internet, I found entertainment on the interwebs.

Don’t Hate the Player

word whomp
I found which was and is a free online games site that caters to the soccer moms everywhere. I was pretty good at one game in particular, Word Whomp, which is a fun vocabulary based game. It’s kind of a cross between Whac a Mole and Scrabble. For each new round, a string of “random” letters is presented along with clusters of blank tiles representing all of the English words one can make from those letters.

Anyway, as I said, I was pretty good at it, but there were people on the leader boards who were inhumanly good at it. Well, I’m a naturally competitive person and was rapidly becoming frustrated and with the repetitiveness of the game and my inability to compete with the leaders. So, what did I do? I cheated. Before I describe how, let’s just have a disclaimer here. While I was cheating, there was no payoff for it other than personal satisfaction. I consider it “harmless cheating.”

Cheaters Never Win?

I fired up Visual Basic, I forget which version now, and created a program which was able to screen scrape Word Whomp for that random letter set. It then scanned through its own local Access Database (Lame… I know) for applicable words which could be made. Each viable word was then submitted. At the end of every round, Word Whomp helpfully lists all the words you missed, and my program scraped those too then added them to the database. Since my database started “empty” it took a bit to gain momentum. However, the longest words are only six characters, so as time went on, my program got more and more efficient at its task and rarely missed a word. Needless to say, it wasn’t long before my program was near the top of the leader boards. However, as time went on, it also became obvious that I wasn’t the only cheater either as I never did end up in first place.

What does this have to do with Biml?

A couple of things occurred to me as I finished the cheating program. I had a lot more fun writing the program to cheat at Word Whomp than actually playing the game itself. I get bored when performing repetitive tasks, even if they’re supposed to be fun. This is one reason why I write so much code that writes other code. I’ve never understood why some folks are content to re-write the same basic code repeatedly.

As I’ve mentioned on the blog and in my recent videos, I adopted Biml Script for a recent project and saved enormous amounts of development time as a result. Granted, there is a learning curve to gain an understanding of Biml Script and deciding how you will leverage it. However, as soon as I passed that stage, my productivity just took off. Although I am used to working myself out of a job, even I wasn’t prepared for just how quickly I finished my last project. Working on my Biml Script framework, is a lot like the Word Whom cheating program. It’s fun and liberating to design new frameworks and methodologies to solve age old problems.

Incidentally, shortly after I finished my program, I grew bored again watching it play Word Whomp. This was about the time that I decided to work my way out of that boring job and started reaching out to members of the PASS Community like Brent Ozar (Blog/Twitter). Since then I’ve put my energy, ingenuity, and drive into becoming a world class Data Warehouse Architect and am now “cheating” with Biml. I urge you to do the same.

HELLO to the WORLD of Biml

A “Hello World” program is an ages old traditional way to teach the basics of syntax for a programming language to a beginner. Since so many of us are just starting to use Biml Script, I thought it would be fun to start out with it. Also, I’ve made the entire solution available for download.


Before we begin, download and install BidsHelper from Then open Business Intelligence Development Studio (BIDS) or SQL Server Data Tools (SSDT) and create a new project. We’ll call ours “HelloWorld”.
Right click on your project name and select “Add New Biml File”.
Open the Biml file and you’ll find this:

<Biml xmlns="">

This is an XML declaration that specifies the XML Schema Definition (XSD). The XSD file defines “the rules” for any XML document created and validates them against those rules. Now, before we start getting into Biml code, I want everyone to read about the Copy and Paste Issues in Visual Studio. Otherwise, any code you copy from my blog (or really any source) and then paste into Visual Studio will not work.

Create a Basic SSIS Package

Since the whole point of “Hello World” is to create the most basic example in a programming language that produces output, the simplest step we can take is to create a package. So, let’s add the bare minimum necessary to create a package called “Hello World”. Begin by hitting enter at the end of the first line and typing “<". Intellisense should kick in with a list of options. You can either continue typing "Packages" or select it from the list. hello_world_intellisense
As soon as you close the Packages Node, you’ll notice that the closed Packages tag appears and the cursor is placed between those tags. Type “<" again and you'll notice that Intellisense offers fewer options this time. Either type or choose "Package" (singular) and then type ">” to close that as well. You should see that your code has a blue squiggly line below “Package” which indicates that something is missing. If you hover over that, you’ll see the following:

Our code is missing a Constraint Mode (and also a Package Name) which is required. So, if we edit our code to add a Name of “Hello World” to our package and then type “ConstraintMode” the Intellisense offers the two options of Linear and Parallel. Linear will cause each Control Flow Task to be run in order, while Parallel allows them to be run concurrently and also allows you to specify the precedent constraints individually per task. For our simple example, just select Linear. Your code should now look like this:

<Biml xmlns="">
		<Package Name="Hello World" ConstraintMode="Linear">

Right click on your Biml Script file, and select “Generate SSIS Packages.” A generic package named “Hello World.dtsx” should be created. If you attempt to open it, you’ll see that the package is empty.

Creating Tasks

So, we’ve achieved the basic premise of a “Hello World” application, but lets dig further. Let’s add the Biml code for a couple of Tasks. First, we’ll add an Execute SQL Task. Now, if you think about a simple Execute SQL Task, what elements would be required?

  • Task Name
  • Connection
  • SQL Statement if using direct input.

So, we need to have a Connection. A Connection requires a name and a connection string, but it also requires an existing database. I’ve created an empty database called “HelloWorldDemo” on my instance. There are a number of ways to generate connection strings, but I prefer the following way because it is so simple. For your convenience I’ve recorded this quick video, but you can also follow the instructions below it.

  1. Create a text file somewhere. I’m lazy so I created mine on my desktop and named it Connection Strings.txt. You may name yours whatever you wish.
  2. Make sure that file extensions are not hidden on your system. Highlight the txt extension and replace it with udl. Windows will warn you that changing the extension might cause problems. Select Yes and change it anyway.
  3. Double Click the file to open it. You’ll be presented with the standard Data Link interface that we’re all familiar with.
  4. Configure that screen, but please select “Use Windows NT Integrated security.” You’ll see why in a moment.
  5. Select the Database, in my case it’s called HelloWorldDemo, and click Test Connection. After that succeeds, select OK to close the screen.
  6. Right Click the udl file and select Open With… Notepad (or some similar program). The last line of text will have your connection string.

Return to your Biml Script and create a Node called Connections just below the first line. Then place the code into it like so:

<Biml xmlns="">
		<Connection Name ="HelloWorldDemo" ConnectionString="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=HelloWorldDemo;Data Source=DAVESDEV\SQL2012" />

Now that we have a connection, we can create Packages and Tasks which use it. Let’s create the Execute SQL Task mentioned above. First, we have to create a Tasks Node. Our Execute SQL Task will connect to our Connection, but won’t actually do anything.

<Biml xmlns="">
		<Connection Name ="HelloWorldDemo" ConnectionString="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=HelloWorldDemo;Data Source=DAVESDEV\SQL2012" />
		<Package Name="Hello World" ConstraintMode="Linear">
				<ExecuteSQL Name="SQL Hello World" ConnectionName="HelloWorldDemo">
					<DirectInput>Select 'Hello World' as GreetingMessage</DirectInput>

Right click on your Biml Script and click Generate SSIS Packages. This time, since the Hello World.dtsx package already exists, you’ll receive the following window which allows you to selectively over-write packages. Obviously, in this case we want to over-write the package so select Commit. Now, if you open your package you will find a single Execute SQL Task and if you double click to open it, you will find the following:

While the package will open, those of us working in SSIS 2012 will receive an error while running it.

You will learn to loathe this.

You will learn to loathe this.

The default ProtectionLevel for a SSIS project is EncryptSensitiveWithUserKey, which most of the time is not what you want. Basically, any user other than the original creator who attempts to use this package must provide all of the credentials. I prefer to use DontSaveSensitive whenever possible and let Active Directory handle permissions. Right click on the project name and select Properties. Change the ProtectionLevel to DontSaveSensitive. If your project still contains the default Package.dtsx, you will need to open that and change it’s ProtectionLevel setting or delete it to continue.

After changing the project ProtectionLevel, go ahead and run Hello World.dtsx. The SQL Hello World task will turn green, and there is peace in the valley. Next, let’s create some tables in Biml.

Creating Tables

Step 1 – Create Definitions

Before we begin creating tables in Biml and Biml Script, let’s just define what is necessary to create the same tables using T-SQL. We’re going to create two tables so I can demonstrate how Biml Script will iterate through them. The two tables will be called “HelloWorld” and “FromMade2Mentor.” Let’s look at the T-SQL that I would write to define these.

Use HelloWorldDemo;
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'dbo')
    EXEC sp_executesql N'CREATE SCHEMA dbo AUTHORIZATION dbo';    
IF  EXISTS (SELECT 1 FROM sys.tables WHERE SCHEMA_NAME(schema_id) = 'dbo' AND name = 'HelloWorld')
   Drop Table  dbo.HelloWorld;
Create Table dbo.HelloWorld
[HwId] smallint IDENTITY(1, 1) NOT NULL,
[HWChar10Field] varchar(30) NULL
IF  EXISTS (SELECT 1 FROM sys.tables WHERE SCHEMA_NAME(schema_id) = 'dbo' AND name = 'FromMade2Mentor')
   Drop Table  dbo.FromMade2Mentor;
Create Table dbo.FromMade2Mentor
[MentorId] smallint IDENTITY(1, 1) NOT NULL,
[MentorDate] date NULL

While I realize that both of these are in the schema dbo and the create schema statement isn’t really necessary, I’ve left it in for two reasons. First, you’d need a statement like that to verify that a named schema like ‘Staging’ already existed before creating tables in it. Second, it illustrates how Biml script is similar to the above statement.

Creating tables in Biml is fairly easy. The important thing to remember is that you must first define the table(s) in a Biml file, and then use another Biml Script file to build the package(s) which you would then run to create them. The end result is a SSIS package with Execute SQL Tasks which create the necessary tables. So, let’s start with the Biml file which will contain the table definitions and we’ll name it Destination Table Defs.biml. As you can see in the following code the Biml follows the same basic path.

  1. The Connection is declared (which isn’t necessary in SSMS but is implied).
  2. The Database(s) is specified.
  3. The Schema(s) is specified.
  4. The Table Name is specified. (as in Create Table dbo.HelloWorld).
  5. The Columns are named and defined for data type and other attributes such as Nullability and Identity.
<Biml xmlns="">
		<Connection Name ="HelloWorldDemo" ConnectionString="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=HelloWorldDemo;Data Source=DAVESDEV\SQL2012" />
		<Database Name="HelloWorldDemo" ConnectionName="HelloWorldDemo" ></Database>
		<Schema Name ="dbo" DatabaseName="HelloWorldDemo"/>
		<Table Name="HelloWorld"   SchemaName="HelloWorldDemo.dbo">
				<Column Name="HwId" DataType="Int16" IdentityIncrement="1"></Column>
				<Column Name="HWVarChar30Field" DataType="AnsiString" Length="30" IsNullable="true"></Column>
		<Table Name="FromMade2Mentor"   SchemaName="HelloWorldDemo.dbo">
				<Column Name="MentorId" DataType="Int16" IdentityIncrement="1"></Column>
				<Column Name="MentorDate" DataType="Date" IsNullable="true"></Column>

Now, save your project, right click on Destination Table Defs.biml and select Check Biml for Errors. Assuming that your script passes validation right click again and select Generate SSIS Packages. What happens? Well, nothing. Why? You have provided the table definitions, but haven’t provided the mechanism to actually create them. That is our next step.

Step 2 – Generate Package(s)

Knowing what we learned earlier, we could create a static Biml file with Execute SQL Tasks to create these tables, but that’s not very efficient. Wouldn’t it be great if we could just dynamically create that package and it would create every table we’ve specified? This is your first taste, small though it is, of the power of Biml Script. Let’s go through the steps to create such a package as if we were going to do it in SSIS. We’d most likely:

  1. Create a new package and name it. I’ll name it “Create Destination Tables.dtsx”.
  2. We’d create and specify a Connection.
  3. For each table we needed to create we’d drag out an Execute SQL Task, rename it, specify the connection, and provide the SQL query to use for Direct Input.
  4. Close and save the package.

Of course, this process is tedious. It’s the same set of clicks and drags over and over again. Also, you’d need to open this package and add another Execute SQL Task every time you added a new table to the destination. Let’s create a new Biml File and name it “Create Dest Tables.biml”.Read through the following Biml Script code and you’ll see that it follows along with the process we’d take in SSIS. Paste this script into our new Biml file.

<#@ template language="C#" hostspecific="true" #>
	<Biml xmlns="">
        <Package Name="Create Destination Tables" AutoCreateConfigurationsType="None" ConstraintMode="Linear">
                <# foreach(var table in RootNode.Tables) {#>
                <ExecuteSQL Name="SQL Create <#=table.Schema #> <#=table.Name#>" ConnectionName="<#=table.Connection.Name#>">
                <# } #>

You may notice that Visual Studio shows you red sqiggles with your C# code. That’s because it is expecting XML only so you’ll have to learn to ignore them. Notice how all of the C# code is held inside of the pound signs. The foreach statement loops though every table defined and assigns it to the table variable I’ve declared. The embedded C# such as #=table.Name # is returning the Name property of the Table object. Therefore, I am dynamically naming my Execute SQL Tasks and correctly specifying them for each iteration. GetTableSQL is a function provided by Varigence which does just what you’d think. Now, right click Create Dest Tables.biml and check for errors. You’ll receive the following:
Basically, the problem is that the code is trying to iterate through the tables, but since we only selected the script file and not the definitions file, there were no Tasks created under the Tasks node. If you select both of the files and check for errors, this should be remedied. After passing that check, make sure that both are selected, right click and generate the package. Upon opening the package, you should see the following:

Now, double click SQL Create dbo HelloWorld. After the window opens, click the ellipses to expand the SQL Statement. After expanding the window to show the entire statement, you’ll notice that the resulting SQL query looks almost identical to the one we created above.

If you run the resulting package, the two tables we defined will now exist in the database.

Just for Fun

Typically a “Hello World” program would pop up that message to the user. While we have mimicked that with Packages and Tables “popping up,” I thought it would be fun to show you a little trick to finish this article. I borrowed this with permission from a friend of mine who just recently started blogging over on his new Biml Geek Blog.

Open our Project again, create a new Biml file, and name it. I’ve chosen to name it Hello World Message.biml. Copy and paste the following code into it.

<Biml xmlns="">
<#@ template language="C#" #>
<#@ assembly name="%WINDIR%\\Microsoft.NET\\Framework\\v2.0.50727\\System.Windows.Forms.dll" #>
<#@ import namespace="System.Windows.Forms" #>
<#MessageBox.Show("Hello World");#>

Remember that the scripting in this article is simply C#. Therefore, we can leverage the language to do a lot more than just iterate with For Each loops. So, one last time, right click the Biml file and select “Generate SSIS Packages” and you should see the following:

Keep on Biml-ing!