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, author, speaker, blogger and chronic volunteer who loves teaching and sharing knowledge. She works as a consultant, technical architect and developer, focusing on Data Warehouse and Business Intelligence projects. She loves sci-fi, chocolate, craft beers, ciders, cat gifs and smilies :)

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

Hi Cathrine

I’ve noticed something strange. I’m referencing a C# file from my “master” BIML file:

The issue is that if I’m editing the BIMLCustomExtensions.cs file, the changes wont be reflected immediately if I try to generate SSIS packages using BIML. It’s like my BIML script has cached the .cs file.. I have to close down my Visual Studio and reopen. Then I can see that the latest changes to the .cs file are included and working.

You know why? I’ve tried looked at %USERPROFILE%\AppData\Local\Microsoft\VisualStudio\14.0\Extensions\ where the BIMLExpress extension is located at, but can’t find anything useful..

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..

Ah! Can you delete the first comment? Your website gave me a 404 error, so posted again not knowing the first already got through :)


Share Your Thoughts?

%d bloggers like this: