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.
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:
- Commercial tools:
- 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
- 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
- BimlOnline: (Beta release – free for a limited time only) It is a Web-based IDE
- 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.
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:
After the extension installation is finished, you should follow these steps to activate BimlExpress to be able to write scripts within Visual Studio:
- Open Visual studio
- Create a new SQL Server Integration Services project
- In the menu strip and under the Extensions menu, go to BimlExpress and click on Options:
- You will be asked to complete a free registration by entering your name and email address:
- Next, you should enter the product key sent to your email and click continue:
After registration, you should see the BimlExpress category within the configuration form:
- 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:
<Connection Name="AdventureWorks" ConnectionString="Server=LAPTOP-485M5KTC;Initial Catalog=AdventureWorks2017;Integrated Security=SSPI;Provider=SQLNCLI11"/>
<Package Name="Sample" AutoCreateConfigurationsType="None" ConstraintMode="Linear">
<Dataflow Name="Extract Table List">
<OleDbSource Name="Get Table List" ConnectionName="AdventureWorks">
<DirectInput>SELECT * FROM sys.tables</DirectInput>
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.
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:
There are plenty of resources online for learning. In this section, we will mention some of the most popular resources:
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.
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
- Data Access Modes in SSIS OLE DB Destination: SQL Command vs. Table or View - October 5, 2021
- SSIS Merge Join vs. Merge Transformation - September 28, 2021
- SSIS Pivot transformation vs. Unpivot transformation - September 8, 2021