IN2BI

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 " ( &quot; )
• single quote '  ( &apos; )
• less than sign  < ( &lt; )
• greater than sign  >  ( &gt; )
• ampersand & ( &amp; )

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 &gt; 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/