Automation and Patterns


Scott Currie

Learn about how thinking in terms of automation and patterns can improve your solution and make your Biml code more effective.

published 08.14.15

last updated 10.01.15

Part of lesson Automation and Patterns.



I promise: Just one last thing before we jump into the technical mechanics of writing Biml code.

Many people at first don't understand why Biml is such a useful tool for SQL Server, SSIS, and SSAS development. Ultimately, it comes down to automation and patterns. Both are critically important for large solutions. Both substantially improve time to delivery, maintainability, and reliability of your solutions. But neither is possible to do well in the standard Microsoft data developer tools (BIDS, SSDT, SSMS, etc.). Let's take a brief look at what we mean by automation and patterns, why they are so important, and discuss a few examples of each.


Automation is all about taking repeatable processes and running them repeatedly without human intervention.

You might think it's strange to suggest that SQL Server has an automation problem. After all, the primary workload of databases is to automatically return structured data in response to repeatable queries. Furthermore, the whole point of SSIS is to create repeatable and automated data movement and transformation jobs. That's all certainly true, but I'm talking about the next level up - at the level of creating those scripts and SSIS packages in the first place.

Think about a cars as an analogy. Replacing horses with cars was a form of automation, but you still need someone to drive that car. Cars automated one level, but left the next level up (the driver) as a manual task. Nothing against cars - the automation they provided changed the world. But they present an opportunity for still further automation by removing the driver from the equation. So if SQL Server, SSIS, and SSAS are cars, think of Biml as the self-driving car.

What kind of things can we automate in SQL Server, SSIS, and SSAS? The list is endless, so I'll offer just a couple of examples:

  1. Suppose you have a process table that needs to be merged into a history table. Most SQL developers would choose to do this with a Merge statement, but as you may know, Merge statement syntax is cumbersome and tedious. However, just given column information, we could automatically generate that Merge statement.
  2. Suppose you need to copy data from a source system to a staging location prior to further processing. If the source and target are on different servers that are not linked, you'll generally want to use an SSIS package with a data flow task to do this. It would be tedious and error prone to manually create a data flow task for every table in the source system. Furthermore, changes to the source system would require you to modify those packages. Alternatively, you can do this by reading the source schema and automatically creating the necessary data flows.

You might be thinking, "I can create that Merge statement with dynamic SQL." Or, "I can create those dataflows using the SSIS API or some other SSIS automation mechanism." That is definitely true. However, Biml is much easier and more maintainable than dynamic SQL and SSIS automation. Furthermore, you can use Biml for BOTH and also for SSAS, rather than having to use a different automation technology for every task in your workflow.


Think of patterns of being like automation when you can only automate a small piece of the task.

This is probably best illustrated by an example. In your organization, you may have some requirements for logging. For every execution of an SSIS package, perhaps you have to log the ID of the first and last row processed, the first and last datetime in a specific column, and the total number of rows processed. You could write down the pattern - both what you're logging and the specific mechanism you use for logging (e.g. row count components and event handlers that call stored procedures). If that pattern was written down in a document as a checklist, a developer could follow that checklist for every package. Consequently, that piece of the package - the logging - is automatable, even if the business logic and other custom code in the package might not be. But with traditional SSIS development tools, that doesn't matter. You still need to hand-write those patterns as if they were non-automatable, custom code.

Alternatively, if you are using Biml, you can write down your patterns as fragments of Biml code that can be easily reused in all of your projects. Using this approach, the developer can still do all the custom coding that is required for the solution, and use the pattern Biml fragments when needed. The best part is that if you ever need to change the pattern, there is no need to manually modify all of your existing packages. Simply change the pattern Biml fragment and rebuild your solution.

Logging is just one pattern. Slowly changing dimensions, inferred members, recycling, and basically anything else you can write down in Biml can also be used as a pattern.


Sometimes we get so accustomed to doing things a particular way, it can be difficult to take a step back to think about doing it differently. Biml offers significant promises of enhanced productivity, maintainability, and reliability. But in order to achieve that, you need to think about your development in terms of the things you can automate and the things you can turn into reusable patterns.

Remember that you'll never be able to automate everything. At the very least, you will need to encode your custom business logic in some format, whether that be SQL, SSIS, Biml, metadata, or something else. But most people find after careful thought that more of their day-to-day work can be automated or described as patterns than they would suspect at first glance.


Complete the lesson Automation and Patterns:

You are not authorized to comment. A verification email has been sent to your email address. Please verify your account.


There are no comments yet.