By: Koen Verbeeck
Overview
Biml or Business Intelligence Markup Language is an open-source XML dialect to generate Integration Services (SSIS) packages.
Biml can be used to create multiple SSIS packages from metadata and eliminate much of the manual work that is normally required in the Visual Studio designer. Biml can assist in data warehouse scenarios, data integration/migration projects or every project where a number of SSIS packages who follow the same pattern need to be created.
When to use Biml
Biml works best if you need to create multiple SSIS packages that all follow the same pattern. In other words, if all packages have roughly the same objective and structure, but maybe only the source and destination table change. The changing “data” between packages we’ll refer to as metadata.
Examples of patterns are:
- Loading a (persisted) staging environment
- Loading data into dimensions or fact tables
- Creating a raw data vault
When not to use Biml
If you only need a handful of packages, it might not be worth it to write the Biml scripts first to generate those packages, especially if you can easily create all those packages by hand in less time. Or if you need to create one complex, unique package for a single occasion, Biml is certainly not the right tool for the job.
Outline
In this tutorial, we will go over the basics of the language and show you how you can generate multiple packages at once. At the end of this tutorial, you will be able to create Biml files that can automatically create packages to stage data from one server to another. The tutorial assumes you already know what SSIS packages are and how they work. If you need a refresher, you can take a look at the SQL Server Integration Services (SSIS) Tutorial or the SQL Server Integration Services SSIS 2016 Tutorial.
The high-level outline is as follows:
- Available tools and installation
- Biml language basics
- Generating a simple package
- BimlScript basics
- Retrieving metadata with GetDatabaseSchema
- Dealing with relational objects
- Biml and the layered approach
- Creating the destination tables
- Generating multiple packages at once
You can use the outline on the left to browse the tutorial or use the arrows “Previous” and “Next”.
Additional Information
- There are already some Biml tips on this website:
Last Update: 2/8/2018
