C# and VB Code Files in Biml

Why Move Code Out of Biml Files

Class nuggets written with '<#+ ... #>' are scoped to the Biml file that contains them. The moment a second Biml file needs the same helper, the choices are to copy the nugget, to pull it into a shared file via an include directive, or to lift the helper into a separate C# or VB code file. Code files are the most maintainable of the three: a single helper can serve many projects, and edits ripple out to every project that references the file.

There are five common techniques for keeping shared logic in one place across Biml projects:

• C# and VB code files
• Include files
• CallBimlScript
• Tiered Biml files
• Transformers (BimlStudio only)

This walkthrough focuses on code files.

The Code Directive

The code directive points a Biml file at an external C# or VB source file. The path can be absolute, relative to the current Biml file, or relative to the BimlExpress install folder:

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

Once the directive is in place, every public type and method in the referenced file is visible to the surrounding Biml as if it had been defined inline.

Two practical advantages fall out of this:

• The helper file lives in source control alongside the Biml that uses it, so a single change updates every project that references it.
• The helper file opens in any editor that understands C# or VB. Visual Studio, Visual Studio Code, and similar editors give full syntax highlighting and IntelliSense even when the surrounding host is the SSDT shell.

From Repeated Inline Logic to a Code File

Consider a staging package that names every staging table with the pattern 'SCHEMA_TABLE' in upper case. Without a helper, the naming expression has to be written out at every reference:

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

A change to the convention forces a find and replace across every Biml file in the project, and any miss leaves a package with the old shape.

Lifting the naming logic into a code file removes the duplication. The helper takes the table node as a parameter and returns the formatted name:

using Varigence.Languages.Biml.Table;

public static class NamingHelpers {

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

}

The Biml file references the helper with a code directive and calls 'NamingHelpers.GetStagingTableName(srcTable)' wherever the staging name is needed:

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

A future change to the convention happens in one place: the body of 'GetStagingTableName'. Every Biml file that references the helper picks up the new format on the next build.

Going Further

The same pattern extends to any reusable logic: column type translations, file path builders, audit annotation lookups, and so on. As the helper library grows, the C# code file becomes the single source of truth for project conventions, and the Biml files stay focused on structure rather than string formatting.