Biml is the Business Intelligence Markup Language. It is an XML-based language used to design SQL Server Analysis Services and SQL Server Integration Services solutions.
Biml offers several advantages over native Integration Services package creation:
Biml is a force multiplier for ETL professionals.
My goals in this talk:
We have customer-specific data in the Adventure Works DW database, and we want to migrate this over to a new copy of the database.
To do this, we need to copy data from all tables which relate somehow to dbo.DimCustomer.
Write an SSIS package to move this data. Considerations:
The first step in automation: know the task.
Building a sample SSIS package, we know we will need:
The ideal package gives us an idea of what we need to accomplish, as well as important package optimizations we will need.
This stub package does not need to be perfect--in fact, we will make considerable changes as we go along. But it's easier to start when you have an end in mind.
Varigance, the company behind Biml, has several tools available to help with Biml development. Get these products at their company website.
Biml Express is a free extension for Visual Studio. It currently supports Visual Studio versions up to 2019.
BimlStudio is a fully-featured IDE with much more functionality than BimlExpress.
This is a paid product which offers a lot more than BimlExpress. It integrates well with Analysis Services, allows you to reverse engineer packages, and makes Biml development much easier.
BimlOnline is a beta product intended to build packages without needing to install Visual Studio at all. Its key benefit is that it can reverse engineer an Integration Services package back into Biml.
During the beta period, this product is free. After it leaves beta, there will be a price associated with it.
BimlFlex allows you to build solutions using metadata and templates, without writing code. This is also a paid product and can be quite expensive, but can help with rapid warehouse development.
In addition to Varigence products, BI Developer Extensions (formerly BIDS Helper) has the ability to generate packages from Biml.
BI Developer Extensions is available in the Visual Studio Gallery and on GitHub.
We could write Biml using hard-coded table names and hand-crafted copy-paste code, but that's not very efficient at all. Let's create a metadata table to store everything we'll need for later.
For a small number of tables, you can easily generate migration data by hand. This is a one-time operation. For a larger number of tables, you might want to script it out.
Biml is just XML. It all starts with a Biml tag:
We can define connections in a separate file to make them easier to find. Biml lets you combine files together after the fact.
Templates let us group together files into "tiers." The Biml engine processes tiers in ascending order, so files with
tier="1" run before
tier="2" and so on. You do not need to number your tiers sequentially--you might want to leave gaps for future growth.
In our code, connections is tier 1 and the project/package file is tier 2.
A simplified form of the package:
The CallBimlScript function lets us reference a separate Biml file. Here, we use it to decide between a delete-and-reload strategy and an incremental load approach. In other places, you can use CallBimlScript to abstract out common functionality.
Let's take a look at the code.
As tables change, we should keep the metadata we created up to date.
To support table changes, we just need to re-generate the packages from the Biml script we created. No additional changes are necessary.
If we want to change the package(s) we create, we can modify the Biml directly.
As the Biml scripts change, we just need to re-generate packages from the scripts. If you built your Biml correctly, you should not need to modify your packages directly.
Treat the packages as binaries and Biml as source code.
You should store your Biml in source control. With the Biml in source control and a Biml-first approach with no post hoc SSIS package changes, packages do not necessarily need to be checked in.
An advantage to Biml over raw SSIS packages is that you can perform meaningful diffs against Biml scripts, something you cannot do with SSIS packages.
Biml is a powerful language. We've seen one use case in which Biml saved dozens of hours of development and maintenance time.
With small tweaks, I've implemented this several times in different guises.
To learn more, go here:
Catallaxy Services consulting: