IN2BI, Microsoft Business Intelligence
data, stories & insights
rss | email | twitter

8 Practical BIML Tips

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.

clip_image002


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:

» Similar Posts

  1. Making your Biml files less complex
  2. SQL Server, SSIS, SSAS and SSRS on ONE Server
  3. Logging in SSIS with BIML 1 – Logging Task Execution

» Trackbacks & Pingbacks

  1. Pingback from BIML and its secret sauce. | Adventures with MS SQL Business Intelligence tools

» Comments

  1. Paul S. Waters avatar

    Hi Marco,

    Great post. I did not know about your second tip, and I am going to start to use it. It will be a big help during presentations about using BimlScript in BIDS Helper.

    Another tip could also be to use the Biml editor on BimlScript.com.

    Thanks,

    Paul

    Paul S. Waters — September 4, 2013 3:37 PM
  2. Steve Powell avatar

    Great post, all useful and all address issues I've encountered.

    Another couple of habits I've found useful are naming the Tasks/Transformations so its easy to tie error messages back to the source BIML. I often prefix the Tasks/Transformations with something that identifies what they are

    EXECSQL, ADOLOOP, FILELOOP, DFLOW, SSASPROC OLEDBSRC, DRVCOL, LKP etc...

    I've also started to add snippets for my common BIML structures like if-then-else or loops over columns.

    It saves a lot of typing on my part especially given my inability to hold the SHIFT and ALT key down correctly.

    Instructions for adding a snippet are at

    msdn.microsoft.com/.../ms165394.aspx

    many thanks

    Steve

    Steve Powell — September 10, 2013 9:12 PM
  3. Marco Schreuder avatar

    @Paul, got this tip from a comment of Scott ... great that I could help ..

    @Steve, great suggestions ... I already played around with the snippets. I like it a lot. Thanx

    Marco Schreuder — September 13, 2013 7:40 PM

Comments are closed