Hadi Fadlallah
Generated Biml script file and Biml Editor window

Getting started with Biml

February 26, 2020 by

In this article, we will give a brief overview of BIML by showing how it can be installed and how we can use it to generate SSIS packages using the BimlExpress tool. Finally, we will provide some good resources to learn this technology.

Introduction

BIML is the abbreviation for Business Intelligence Markup Language, which is an XML dialect used to create and configure Microsoft SQL Server Integration Services packages and Analysis Services projects.

This language was released for the first time in 2009 by Varigence, a company founded by former Microsoft employee Scott Currie.

There are many free and commercial tools and components related to Biml:

  1. Commercial tools:
    1. BimlStudio: (formerly Mist) The only development IDE
    2. BimlFlex: A framework that contains a collection of templates, metadata definitions, and tooling that enables you to build and develop data warehouses
  2. Free tools:
    1. BimlExpress: A free extension for visual studio that allows the developer to write and deploy scripts. This extension works with Visual Studio 2010 and newer versions
    2. BI Developer extensions: (formerly BIDSHelper) This extensions package was the first place where BIML features were released, but for now, Varigence has stopped providing updates for BI Developer Extensions, so these features are deprecated and it is recommended to use BimlExpress since it contains more features
    3. BimlOnline: (Beta release – free for a limited time only) It is a Web-based IDE
    4. BimlScript: BimlScript is a language extension where C# or VB.NET code nuggets can be embedded directly within the markup language

In the next sections, we will illustrate how to install and activate BimlExpress and how to create an SSIS package.

Installing BimlExpress

To install BimlExpress, you should download the BimlExpress extension for the visual studio from the official page. After it is downloaded, open the .vsix file installed, which will open the extension installer.

First, you should select the Visual studio installation you want to update:

BimlExpress extension installation wizard first form

Then you should click on “Install” to start the installation of the extension and any needed prerequisites:

BimlExpress extension installation wizard second  form

At the end, you should be notified that BimlExpress extension is installed successfully:

BimlExpress extension installation wizard third form

Activating BimlExpress

After the extension installation is finished, you should follow these steps to activate BimlExpress to be able to write scripts within Visual Studio:

  1. Open Visual studio
  2. Create a new SQL Server Integration Services project
  3. In the menu strip and under the Extensions menu, go to BimlExpress and click on Options:

BimlExpress extension menu strip

  1. You will be asked to complete a free registration by entering your name and email address:

BimlExpress activation form

  1. Next, you should enter the product key sent to your email and click continue:

BimlExpress product key input form

  1. After registration, you should see the BimlExpress category within the configuration form:

    BimlExpress configuration

  1. Change the “Enable BIDS Helper Menus” to “True”

Creating a package from scratch

To create a package using Biml, go to the solution explorer and right-click on the project or any empty package. You will find that there is a new item added within the context menu strip:

Add new Biml File in the SSIS solution explorer

Click on “Add New Biml File”, a new BimlScript file is added to the miscellaneous folder and a new editor window will appear:

Generated Biml script file and Biml Editor window

You can copy and paste the following example taken from BI developer extension samples:

Or you can refer to the following websites to know about some more examples:

At the end, right-click on the BimlScript file and click “Check Biml for errors” to validate the script and to ensure that it is error-free.

How to check Biml for errors

If the script is free of errors, then you will be notified with a message box, as shown below:

error notification message box

In the end, right-click on the BimlScript file and click on “Generate SSIS Packages” to deploy the script you developed.

Generating SSIS package from Biml Script

Finally, a new package is generated, as shown in the image below:

Generated package from Biml Script

Resources

There are plenty of resources online for learning. In this section, we will mention some of the most popular resources:

BimlScript.com

Varigence created BimlScript community, which is a website for tutorials, QA and walkthrough. This resource is considered the first place you should visit to get started with Business Intelligence Markup Language.

The Biml Book: Business Intelligence and Data Warehouse Automation

This book is published by Apress and it is written by a group of data experts such as Andy Leonard, Bill Fellows and Scott Currie (the language creator). It illustrates how to build frameworks and use advanced features to develop SQL Server Integration Services (SSIS), Transact-SQL (T-SQL), and SQL Server Analysis Services (SSAS).

BI Developer Extensions tutorials

Even if BIML features in BI Developer Extensions were deprecated, it is still widely used and it contains some valuable tutorials and samples.

Conclusion

In this article, we have explained what is Business Intelligence Markup Language, and we listed some of the related tools and components. Then, we provided a step by step guide to install and activate BimlExpress extension and how to generate SSIS packages from the Biml script. Finally, we mentioned some of the most popular resources that developers can refer to in order to learn this technology.

Table of contents

Getting started with Biml
Converting SSIS packages to Biml scripts
Using Biml scripts to generate SSIS packages
Extending Biml with C# scripts
Using external C# script files within Biml
Biml alternatives: Building SSIS packages programmatically using ManagedDTS
Biml alternatives: Building SSIS packages programmatically using EzAPI

Hadi Fadlallah
1,073 Views