Skip to content

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

Biml (Business Intelligence Markup Language) - C#/VB Code Files

Do 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:

<#@ code file="NamingStandards.cs" #>

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:

LoadAllTables.biml:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <# foreach (var table in RootNode.Tables) { #>
      <Package Name="Load_<#=table.Schema.Name.ToUpper()#>_<#=table.Name.ToUpper()#>" ConstraintMode="Linear">
        <Tasks>
          <ExecuteSQL Name="Truncate <#=table.Schema.Name.ToUpper()#>_<#=table.Name.ToUpper()#>" ConnectionName="Destination">
            <DirectInput>TRUNCATE TABLE dbo.<#=table.Schema.Name.ToUpper()#>_<#=table.Name.ToUpper()#></DirectInput>
          </ExecuteSQL>
          <Dataflow Name="Load <#=table.Schema.Name.ToUpper()#>_<#=table.Name.ToUpper()#>">
            <Transformations>
              <OleDbSource Name="Source <#=table.SsisSafeScopedName#>" ConnectionName="Source">
                <ExternalTableInput Table="<#=table.SchemaQualifiedName#>" />
              </OleDbSource>
              <OleDbDestination Name="Destination <#=table.Schema.Name.ToUpper()#>_<#=table.Name.ToUpper()#>" ConnectionName="Destination">
                <ExternalTableOutput Table="dbo.<#=table.Schema.Name.ToUpper()#>_<#=table.Name.ToUpper()#>" />
              </OleDbDestination>
            </Transformations>
          </Dataflow>
        </Tasks>
      </Package>
    <# } #>
  </Packages>
</Biml>

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:

NamingStandards.cs:

using Varigence.Languages.Biml.Table;

public static class NamingStandards {

  public static string GetTableName(AstTableNode table) {
    return table.Schema.Name.ToUpper() + "_" + table.Name.ToUpper();
  }

}

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

<#@ code file="NamingStandards.cs" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <# foreach (var table in RootNode.Tables) { #>
      <Package Name="<#=NamingStandards.GetTableName(table)#>" ConstraintMode="Linear">
        <Tasks>
          <ExecuteSQL Name="Truncate <#=NamingStandards.GetTableName(table)#>" ConnectionName="Destination">
            <DirectInput>TRUNCATE TABLE dbo.<#=NamingStandards.GetTableName(table)#>
            </DirectInput>
          </ExecuteSQL>
          <Dataflow Name="Load <#=NamingStandards.GetTableName(table)#>">
            <Transformations>
              <OleDbSource Name="Source <#=table.SsisSafeScopedName#>" ConnectionName="Source">
                <ExternalTableInput Table="<#=table.SchemaQualifiedName#>" />
              </OleDbSource>
              <OleDbDestination Name="Destination <#=NamingStandards.GetTableName(table)#>" ConnectionName="Destination">
                <ExternalTableOutput Table="dbo.<#=NamingStandards.GetTableName(table)#>" />
              </OleDbDestination>
            </Transformations>
          </Dataflow>
        </Tasks>
      </Package>
    <# } #>
  </Packages>
</Biml>

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. My follow-up blog post describes how to create and use C# Extension Methods in Biml :)

About the Author

Cathrine Wilhelmsen is a Microsoft Data Platform MVP, BimlHero Certified Expert, Microsoft Certified Solutions Expert, international speaker, author, blogger, and chronic volunteer who loves teaching and sharing knowledge. She works as a Senior Business Intelligence Consultant at Inmeta, focusing on Azure Data and the Microsoft Data Platform. She loves sci-fi, chocolate, coffee, craft beers, ciders, cat gifs and smilies :)

Comments

Hi! This is Cathrine. Thank you so much for visiting my blog. I'd love to hear your thoughts, but please keep in mind that I'm not technical support for any products mentioned in this post :) Off-topic questions, comments and discussions may be moderated. Be kind to each other. Thanks!

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

So is it possible to put the code of a script task into a separate file, and include it in different s in the BIML file to effect something similar to having a custom SSIS task w/o actually creating a custom SSIS task that componentizes the script task code?

Yes, take a look at Include Files or CallBimlScript :)

Hi! This is Cathrine (again). Just a reminder. I'd love to hear your thoughts, but please keep in mind that I'm not technical support for any products mentioned in this post :) Off-topic questions, comments and discussions may be moderated. Be kind to each other. Thanks!

Leave a Reply to Emil Vissing Cancel reply