You can leverage Business Intelligence Markup Language – or BIML – to automate the creation of Microsoft SQL Server Integration Services (SSIS) Packages. The popular open source BIDSHelper project includes Biml functionality, enabling anyone to write and execute BIML code for free. Among professional SSIS Developers BIML is quickly gaining popularity.
In this post I’ll share some practical tips ….
1. Better copy and paste experience
The irritating behavior of the XML editor in Visual Studio when copying BIML script and how to overcome it has been documented on several blogs. In this overview of tips I couldn’t discard it.
In Visual Studio / SSDT … Select Options in the Tools menu.
In the Treeview of the Options form expand Text Editor, expand XML and choose Formatting.
Next uncheck both options under Auto Reformat.

2. Put directive tags #@..# at the bottom of your BIML file
In many examples (including mine) these directive are placed at the top of the BIML file. Which makes sense because this is the default location in software development. However when Visual Studio opens a file with a directive before the <Biml> xml tag it doesn’t use the xml editor and we lose the formatting and intellisense features in the gui.
So instead place the directives at the bottom of the file. After the closing </Biml> tag. This will not have any effect on the creation of packages.
3. Vanilla Biml file
Before using script in a BIML file create a working BIML file that can create a package with more than 80% of the expected functionality.
Copy this file and use it as a base and then start scripting.
Why? The Combination of xml and code in one document makes it more complicated to select the correct BIML elements and attributes. Next use a small dataset so when you test your work only a small amount of packages are created.
4. Test and save often
During development regularly often check your work. Use the options: “Check Biml for Errors” or “Generate SSIS Packages” from the context menu. This way you not only test your work but save it as well.
Why? Debugging BIML files is mostly a pain. Error messages are limited and often refer to the wrong row and small typos can have a huge impact. So you better find your errors early in development.
5. Special XML characters
Xml has some special characters that you need to enclose in a CDATA tag or replace the special character with its escape code:
- double quote ” ( " )
- single quote ‘ ( ' )
- less than sign < ( < )
- greater than sign > ( > )
- ampersand & ( & )
As an example suppose you have the following BIML:
<Direct Input>
SELECT Name FROM dbo.People WHERE Age > 25
</Direct Input>
then the xml processor will fail at Age > 25 As a remedy change your BIML into:
<Direct Input>
<![CDATA[SELECT Name FROM dbo.People WHERE Age > 25]]>
</Direct Input>, or
<Direct Input>
SELECT Name FROM dbo.People WHERE Age > 25
</Direct Input>
6. Special C# characters
C# also as some special characters that you will need to escape with a backslash . Most notably:
- the backslash itself \ ( \\ )
- single quote ‘ ( \’ )
- double quote “ ( \” )
As an example escaping the backslash in a file location
string FileName = “C:\\Dir\\File.txt”;
or use the verbatim string construction:
string FileName = @”C:\Dir\File.txt”;
7. Learn some basic C#
C# is the principal language of the .NET framework and is widely used for all sorts of programs: Web Services, Web Applications, Windows Form applications, SSIS Scripts, SQL CLR Stored Procedures etc. An investment in learning some C# will pay off. There is an abundant supply of websites and books with relevant information.
To get you started: read the chapters Basics and Flow control off This tutorial: http://zetcode.com/lang/csharp/
8. Learn from the samples
Steal / use the samples on:
- Varigence samples http://varigence.com/Documentation/Samples/Biml
- BimlScipt.com
http://bimlscript.com/ - My blog posts on BIML
http://blog.in2bi.com/biml/ - Andy Leonard’s Stairway to Biml on SQLServerCentral
(sign up mandatory)
http://www.sqlservercentral.com/stairway/100550/ - The series John Welch wrote on the BIML functionality in BIDS Helper
http://agilebi.com/jwelch/2011/05/13/biml-functionality-in-bids-helper/ - The blog of John MinkJan on BIML
http://biml101.blogspot.nl/