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:

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

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

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:

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

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

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

   

7. 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:

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

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:

• Example from Varigence
• Execute SQL Task example

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.

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

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

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

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

• Author
• Recent Posts

Hadi Fadlallah

Hadi is an SQL Server professional with more than 10 years of experience. His main expertise is in data integration. He's one of the top ETL and SQL Server Integration Services contributors at Stackoverflow.com . Also, he published several article series about Biml, SSIS features, Search engines, Hadoop, and many other technologies.

Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a MongoDB, Neo4j, and ArangoDB certified professional.

On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality assessment techniques.

Hadi really enjoys learning new things everyday and sharing his knowledge. You can reach him on his personal website.

View all posts by Hadi Fadlallah

Latest posts by Hadi Fadlallah (see all)

• An overview of SQL Server monitoring tools - December 12, 2023
• Different methods for monitoring MongoDB databases - June 14, 2023
• Learn SQL: Insert multiple rows commands - March 6, 2023