Saturday, 22 June 2013

Starting with BIML for SSIS

Yesterday I finally started using BIML in one of my projects. Very handy to create dozens of those simple but annoying packages in a fraction of the time you would normally need when creating them manually. No mistakes when creating your thirtieth staging package and all according to the naming conventions.

But two things bothered me and I think more people find them annoying:

1) Copy & Paste
When you copy and paste something from an other script, Visual Studio adds extra chars and the BIML Script doesn't work: Directive value without name.
<#@ template language="C#" hostspecific="true"#>

<#@ template="" language="C#" hostspecific="true"#>
Copy and paste error: adding =""

Directive value without name

You van overcome this by changing the XML formatting options in Visual Studio. Go to the Tools menu and choose options. Then go to the Text Editor options, XML and formatting. There you must change auto format on paste from clipboard. Now try again pasting some BIML code. See this for more details.

disable on paste from clipboard

2) Losing format & intellisense
When you mix the XML from BIML Script with C# code, the XML code obviously gets corrupt, because the brackets are not opening and closing one after the other:
<ExecutePackage  Name="EPT - <#=row["name"]#>">

And then, when you re-open the BIML Script, you have lost formatting and even worse you have lost intellisense.

Formatting and intellisense gone!

You can overcome this by right clicking the BIML Script and select Open With... Then choose the XML (Text) Editor. Now it opens with formatting and intellisense. Setting it as the default editor didn't work.
Open in XML (Text) Editor

An alternative is using the online editor at This editor doesn't have the formatting issues and the <# and #> are formatted properly.

UPDATE: easier solution, move imports to bottom.

Next BIML blog post: Mixing BIML with .Net code

No comments:

Post a Comment

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Related Posts Plugin for WordPress, Blogger...