Don’t Repeat Your Biml – C#/VB Code Files

Biml (Business Intelligence Markup Language) - C#/VB Code FilesDo you use C#/VB classes and methods in your Biml projects? One solution is to create class nuggets <#+ … #>, but they can only be used in their containing Biml file. You can move class nuggets to separate files and include those files, but a more elegant solution is to use C#/VB Code Files.

In addition to using C#/VB Code Files, there are four other main ways you can avoid repeating your Biml code:

In this post, we will look at how to use C#/VB Code Files.

C#/VB Code Files

The code directive allows you to reference C#/VB code files by using an absolute path, a path relative to the current file, or even a path relative to the BimlExpress installation folder:

By using C#/VB code files, you can easily apply the Don’t Repeat Yourself software engineering principle. Create your own library of C#/VB classes and methods, centralize the files, and reference them in many projects. This allows you to update your code in one file to make changes to all projects.

Another benefit of using C#/VB code files is that you can manage your code separately from your Biml projects and use your favorite editor with syntax highlighting and intellisense. If you have the full version of Visual Studio installed you get the full code editor when you open up your C#/VB code file. If you only have the SQL Server Data Tools (SSDT) shell installed, there are many free editors available you can use, from Notepad++ to Visual Studio Code or even Visual Studio Community Edition.

Biml C#/VB Code Files

Example: From No Code to C# Code File

Below is a simplified example where we want to use the naming standard SCHEMA_TABLE for our staging tables. We can solve this without using any C#/VB classes and methods, but we end up duplicating the code <#=table.Schema.Name.ToUpper()#>_<#=table.Name.ToUpper()#> many places:

If we want to change the naming standard, we would have to change the same code in many places. A Find and Replace would help us, but what if we have many files or many projects using the same naming standard? We could end up having to Find and Replace many times and maybe even forget a file.

Instead of repeating our code or repeating our manual actions, we can move the logic into a code file with a class and method. This method takes the table as a parameter:

Then we add the code directive, and call the class and method instead by using <#=NamingStandards.GetTableName(table)#>, passing the table as a parameter:

If we want to change the naming standard now, all we have to do is update a single line of code in the code file :)

But wait, there’s more!

The C# code example above can be simplified and turned into an extension method to make it even easier to use. A blog post about extension methods will be posted soon. Stay tuned! :)

Who is Cathrine Wilhelmsen?

Cathrine is a Microsoft Data Platform MVP, BimlHero, Microsoft Certified Solutions Expert, author, speaker, blogger and chronic volunteer who loves teaching and sharing knowledge. She works as a consultant, architect and developer, focusing on Data Warehouse and Business Intelligence projects. She loves sci-fi, chocolate, coffee, craft beers, ciders, cat gifs and smilies :)

4 thoughts on “Don’t Repeat Your Biml – C#/VB Code Files”

Hi Cathrine

In my main BIML file, I’m refering to a .cs file like this:

I’ve noticed something strange though. If I edit my BIMLCustomExtensions.cs file with some new changes/small modifications and straight afterwards run the “Generate SSIS Packages” command from my main BIML file, the changes are not reflected in my BIML output. How come?

Its like the BIML file has cached my .cs file and wont get the last changes unless i actively close my visual studio, reopen and then run the Generate SSIS Packages.. Which is pretty annoying :-(

i’ve tried looking if it somehow cached the .cs file somwhere, like %USERPROFILE%\AppData\Local\Microsoft\VisualStudio\14.0\Extensions\ under the BIMLExpress extensions, but can’t find anything relevant..

Hi Emil, I actually don’t know what’s causing this, so I’m afraid I’m not much help. Does this also happen if you click Update in the Preview Pane and check that the changes are reflected in the Biml before generating SSIS packages?

Hi again Cathrine,

I somehow managed to get around the above issue.

I have another question though. What if I want to reference an external Microsoft .assembly – from my .cs file ? How would you go on about that .. :)

Got some pointers from going through your BIML book – so far it’s a really good read! Thanks

Share Your Thoughts?

This site uses Akismet to reduce spam. Learn how your comment data is processed.