Don't Repeat Your Biml - CallBimlScript

February 23, 2015

Biml

Biml, Don't Repeat Yourself

5 min

Biml Wheel.

Are you using Biml so you won’t have to do the same tasks over and over and over again in multiple SSIS packages? If so, you probably don’t want to write the same Biml code over and over and over again either. Instead, you can move common code to separate files, centralize and reuse these files in many projects, and update code in one file to make changes to all projects. One of the ways to apply this Don’t Repeat Yourself software engineering principle in Biml is to use CallBimlScript.

In addition to using CallBimlScript, there are four other main ways you can avoid repeating your Biml code:

Include Files
Tiered Biml Files
C#/VB Code Files
Transformers (BimlStudio feature only)

In this post we will look at how to use CallBimlScript with Parameters.

CallBimlScript with Parameters

If you come from the world of SQL, you will see that using CallBimlScript is similar to using a stored procedure. If you come from the world of programming, you will see that CallBimlScript is similar to a method or function, but with some differences.

The Biml file with CallBimlScript (the Caller) passes parameters to another Biml file (the Callee). The Callee can then use these input parameters as regular variables and to control logic. The order you specify the parameters in CallBimlScript in the Caller must match the order you have specified the parameter properties in the Callee. Below is a very simple example of a caller and a callee:

Caller.biml:

<Packages>
  <#=CallBimlScript("Callee.biml", StringParameter, IntParameter)#>
</Packages>

Callee.biml:

<#@ property name="StringParameter" type="String" #>
<#@ property name="IntParameter" type="Int32" #>
<Package Name="<#=StringParameter#>_<#=IntParameter#>" />

You use an Expression Control Block (that starts with the equal sign <#= … #>) when you want to use CallBimlScript. Expression Control Blocks first parse the content inside the block as a string, and then the Expression Control Block is replaced by that string. It is important that the callee only contains the code that can replace the Expression Control Block without causing invalid syntax errors. In the example above, CallBimlScript is used inside , so the callee must only contain elements.

One of my favorite things about CallBimlScript is that you are not limited to using simple parameter types like String, Int32 or Boolean. You can pass all kinds of parameters! Just think about the cool solutions you can create by passing parameters such as AstTableNode, List<> or DataTable :)

In BimlExpress, you only need to right-click on Caller.biml and click Generate SSIS Packages, and it will locate and use Callee.biml. If the callee is located outside your project, you will have to use an appropriate file path when using CallBimlScript.

How does CallBimlScript work?

1. The Caller (left) has a line of CallBimlScript code. In this example, it has a blue parameter:

Left file with five lines of code, right file with two groups of three lines of code.

2. The Caller (left) passes the blue parameter to the Callee (right):

Arrow pointing from the left file’s middle line to the right file’s top three lines.

3. The Callee might execute some logic based on the parameter, such as an if / else statement:

The right file’s top three lines pointing to themselves.

4. The Callee returns the evaluated code to the Caller. In this example, the blue parameter only returns the blue lines of code from the Callee:

Arrow pointing from the right file’s top three lines to the left file’s middle line.

5. The line of CallBimlScript code in the Caller is replaced with the blue lines of code from the Callee:

The left file’s middle line has been replaced with the three lines from the right file.

Example: CallBimlScript to create Packages

Below is an extended example where I have created a callee that creates a Truncate / Load package. It accepts four parameters: AstTableNode, source connection, destination connection and destination schema. I can reuse this callee in all my projects where I Truncate / Load tables, without having to copy and paste the actual code. If I want to change the pattern, for example if I want to add a second derived column, I will only have to change it in that one callee Biml file. The CallBimlScript specific code lines are highlighted:

<# var sourceConnection = SchemaManager.CreateConnectionNode("Source", "Data Source=.;Initial Catalog=SourceDatabase;Provider=SQLNCLI11.1;Integrated Security=SSPI;"); #>
<# var sourceMetadata = sourceConnection.GetDatabaseSchema(ImportOptions.ExcludeViews); #>

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="Source" ConnectionString="Data Source=.;Initial Catalog=SourceDatabase;Provider=SQLNCLI11.1;Integrated Security=SSPI;" />
    <OleDbConnection Name="Destination" ConnectionString="Data Source=.;Initial Catalog=DestinationDatabase;Provider=SQLNCLI11.1;Integrated Security=SSPI;" />
  </Connections>

  <Packages>
    <# foreach (var table in sourceMetadata.TableNodes) { #>
      <#=CallBimlScript("Callee.biml", table, "Source", "Destination", "stg") #>
    <# } #>
  </Packages>
</Biml>

<#@ property name="Table" type="AstTableNode" #>
<#@ property name="SourceConnection" type="String" #>
<#@ property name="DestinationConnection" type="String" #>
<#@ property name="DestinationSchema" type="String" #>

<Package Name="Load_<#=Table.Schema.Name#>_<#=Table.Name#>" ConstraintMode="Linear">
  <Variables>
    <Variable Name="NewRows" DataType="Int32">0</Variable>
  </Variables>
  <Tasks>
    <ExecuteSQL Name="SQL Truncate <#=Table.Schema#> <#=Table.Name#>" ConnectionName="<#=DestinationConnection#>">
      <DirectInput>TRUNCATE TABLE <#=DestinationSchema#>.<#=Table.Schema#>_<#=Table.Name#></DirectInput>
    </ExecuteSQL>
    <Dataflow Name="Load <#=Table.Schema#> <#=Table.Name#>">
      <Transformations>
        <OleDbSource Name="Source <#=Table.Schema#> <#=Table.Name#>" ConnectionName="<#=SourceConnection#>">
          <ExternalTableInput Table="<#=Table.SchemaQualifiedName#>" />
        </OleDbSource>
        <DerivedColumns Name="Add LoadDate">
          <Columns>
            <Column Name="LoadDate" DataType="DateTime">@[System::StartTime]</Column>
          </Columns>
        </DerivedColumns>
        <RowCount Name="Count NewRows" VariableName="User.NewRows" />
        <OleDbDestination Name="Destination <#=Table.Schema#> <#=Table.Name#>" ConnectionName="<#=DestinationConnection#>">
          <ExternalTableOutput Table="[<#=DestinationSchema#>].[<#=Table.Schema#>_<#=Table.Name#>]" />
        </OleDbDestination>
      </Transformations>
    </Dataflow>
  </Tasks>
</Package>

Example: CallBimlScript to control SSIS Logging

Below is an extended example where I have created a callee that accepts a single parameter called LogType. Based on the value of the LogType parameter, the SSIS package will log some or all events. This logging code is particularly verbose (I have shortened it in this example) and I absolutely do not want to copy, paste and maintain it in multiple files. With CallBimlScript, it is very easy to change! I can just change the value of the LogType parameter from “LogErrors” to “LogAll”, regenerate my SSIS packages, and all the logging will be updated :)

<#=CallBimlScript("CalleeLogging.biml", "LogErrors")#>

<#=CallBimlScript("CalleeLogging.biml", "LogAll")#>

<#@ property name="LogType" type="String" #>
<LogEvents>
  
  <# if (LogType == "LogErrors" || LogType == "LogAll") { #>
    <LogEvent EventName="OnWarning">...</LogEvent>
    <LogEvent EventName="OnError">...</LogEvent>
    <LogEvent EventName="OnTaskFailed">...</LogEvent>
  <# } #>
  
  <# if (LogType == "LogDiagnostic" || LogType == "LogAll") { #>
    <LogEvent EventName="Diagnostic">...</LogEvent>
    <LogEvent EventName="DiagnosticEx">...</LogEvent>
  <# } #>
  
  <# if (LogType == "LogAll") { #>
    <LogEvent EventName="OnInformation">...</LogEvent>
    <LogEvent EventName="OnExecStatusChanged">...</LogEvent>
    <LogEvent EventName="OnVariableValueChanged">...</LogEvent>
    <LogEvent EventName="OnPreValidate">...</LogEvent>
    <LogEvent EventName="OnPostValidate">...</LogEvent>
    <LogEvent EventName="OnPreExecute">...</LogEvent>
    <LogEvent EventName="OnPostExecute">...</LogEvent>
    <LogEvent EventName="OnProgress">...</LogEvent>
    <LogEvent EventName="OnQueryCancel">...</LogEvent>
  <# } #>
  
</LogEvents>

Summary

CallBimlScript works like a parameterized include file, or like a SQL Server stored procedure. The file to be called (“callee”) specifies accepted parameters: <#@ property name=“Parameter” type=“String” #>. The file that calls (“caller”) passes parameters: <#=CallBimlScript(“Callee.biml”, Parameter #>. The CallBimlScript block in the caller will be replaced by the returned contents of the callee file.

If you only need to include code without using parameters or controlling logic, you can use Include files instead.

About the Author

Professional headshot of Cathrine Wilhelmsen. 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 🤓