By: Andy Kim

 

What is Biml?

Business Intelligence Markup Language (Biml) is an XML-based language that can generate (SQL Server Integration Services) SSIS packages, amongst other things. The programming language was developed by Scott Currie and his company Varigence. Here is a tiny snippet of what Biml looks like in Visual Studio:
 

Image credit: https://docs.varigence.com/bimlexpress/index
 

General Details

Biml is the name for the language but the technology actually comes embedded within three different products offered by Varigence. I have experience with the free product BimlExpress, which is a Visual Studio extension (.vsix). This is the product that I will be referring to in this post. BimlExpress is used within the Microsoft ecosystem because it depends on Visual Studio, SQL Server Data Tools (SSDT), SQL Server, and the C# language.
 

How can Biml benefit your organization?

1) High-speed SSIS package generation

The most obvious benefit of using Biml is the speed in which you can develop SSIS packages. From experience, it can take eight or more hours just to develop a single complex package manually. When I refer to a complex package, I mean a package that:

• loads large enterprise data warehouses using Kimball-style patterns
• is designed to load tables with billions of records and upwards of 300 columns in some cases
• can be loaded with up to 80 or more foreign key lookups, some of which utilize cache lookups
• handles inferring members
• is integrated into a batching framework
• is integrated into a custom auditing framework
• can handle more complex load scenarios like un-pivoting in some cases

 

 

When you juxtapose this manual development process against generating a package using Biml, you can easily see it is very quick and cool to be able to right-click on a Biml file, click “Generate SSIS Packages”, and watch as the computer whisks away and magically generates SSIS package(s) before your eyes.

The ability to generate packages at this speed does come with a cost, however. There is an upfront initial cost to develop the Biml template(s), although it can pay off big time afterwards.
 

Mini anecdote

I am currently working on a large project where my small team needs to generate almost 2000 packages in under 3 months. The only way we can even have a chance at meeting this deadline is by leveraging Biml.
 

2) Maintaining a level of standards and consistency in your SSIS packages

A second and slightly less obvious benefit of using Biml is that it can help maintain a level of consistency and standards in your packages.

Is each developer:

• accomplishing tasks in inconsistent ways?
• writing queries in inconsistent ways?
• naming their variables in inconsistent ways?
• naming the packages in inconsistent ways?

If you leverage Biml to systematically define these items they will remain consistent across the board. This can provide several different benefits in addition to everything just being cleaner.
 

Would using Biml benefit your organization?

Considering the initial Biml development time costs

As I mentioned above, there is an upfront cost to the initial development of the Biml template(s). Depending on the complexity of your packages, this could take anywhere from hours to days to months.

If the initial effort to develop the Biml will outweigh the manual package development effort, then it is not worth it. If you only need to develop a few packages, and you could manually develop them in less time than it would take to develop the initial Biml, then it is not worth it.

In some circumstances, the best option is to do a hybrid approach where you use Biml to get you most of the way to package completion, then manually finish developing the rest of the packages. It all boils down to which method will help you achieve your goals.
 

 

Level of variance within the packages you need to generate

The level of variance in the packages that you need to develop is directly related to the initial Biml development cost. How many different “flavors” of Biml templates would you need? The more flavors you need, the more development time you will need. For example, if you have 30 packages that do Analysis Services cube tasks, and another 30 packages that do ETL, and then you will need to develop 2 different “flavors” of Biml templates. So be sure to factor that in to the initial development cost.
 

Other considerations

Other considerations include items like your team size, their skillset, and your specific time constraints. You will need a team who understands SQL Server, SSIS, Visual Studio, XML, and C#.
 

Ideal situation

When you need to generate a large number of packages (hundreds to thousands) and these packages have repeatable design pattern(s), this is where Biml will really shine.
 

How do I get started with Biml?

If you are interested in experimenting with Biml, you can download and install here:
BimlExpress – https://docs.varigence.com/bimlexpress/index

To be powerful, you will need a strong metadata store populated with the information you need to help automatically generate the packages. As you could imagine, you would need to keep metadata about items such as:

• a list of tables
• a list of column mappings
• a list of foreign key lookups
• a list of keys (primary keys, alternate keys, surrogate keys, etc.)
• a list of indexes

This kind of metadata can be queried from the back-end of data modelling software or could be created from scratch, if necessary.

Here is a link that goes a little more into detail on what a more complete Biml solution might look like:
https://www.mssqltips.com/sqlservertip/3124/generate-multiple-ssis-packages-using-biml-and-metadata/

If you ever really get your hands dirty with Biml, chances are you will keep running into the names
Catherine Wilhelmsen and Andy Leonard. Both provide a lot of great knowledge and resources to the community, so a big shout out and thanks to them!

I would like to give a special shout out to the Varigence Support Team for being helpful and being very responsive in the past when I was encountering various licensing and functionality issues. A big shout out and thanks to them!
 

Conclusion

Biml can be a powerful tool to help you automate generating SSIS packages, but it is not for everyone. If you need to generate lots of packages in a short amount of time, and they have a repeatable design pattern, then it is definitely worth taking a look at this technology.

As I mentioned above, Varigence has other products, but they are not free. Sometimes it can be challenging to get funding approved for new software licenses, so you have to work with what you got.

I have not been able to try their BimlStudio or BimlFlex products, but they probably make the development even easier and faster than using the free product BimlExpress. I would love to try those products out in the future.

If you think your organization needs to harness this technology, but lacks the personnel resources, then Key2 has experts who can help. Please give us a call for a free consultation.
 

Do you have questions?

Thanks for reading. We hope you found this blog post to be useful. Do let us know if you have any questions or topic ideas related to BI, analytics, the cloud, machine learning, SQL Server, (Star Wars), or anything else of the like that you’d like us to write about. Simply leave us a comment below, and we’ll see what we can do!
 

Keep Your Business Intelligence Knowledge Sharp by Subscribing to our Email List

Get fresh Key2 content around Business Intelligence, Data Warehousing, Analytics, and more delivered right to your inbox!

Grow your analytics knowledge.

Leave this field empty if you're human:

 

---

Key2 Consulting is a data warehousing and business intelligence company located in Atlanta, Georgia. We create and deliver custom data warehouse solutions, business intelligence solutions, and custom applications.