Don't Repeat Your Biml - 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:

• Include Files
• CallBimlScript
• Tiered Biml Files
• Transformers (BimlStudio feature only)

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.

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, international speaker, author, blogger, organizer, and chronic volunteer. She loves data and coding, as well as teaching and sharing knowledge - oh, and sci-fi, gaming, coffee and chocolate 🤓