Skip to main content

CallBimlScript for Reuse

Why CallBimlScript​

Biml exists to avoid repeating SSIS work, and that same principle applies to the Biml itself. Common patterns such as a truncate and load package, a logging block, or a standard set of derived columns should not be copied across files. CallBimlScript lets a caller file invoke another Biml file as if it were a function, passing parameters in the same way a stored procedure receives them. Updating one callee file then updates every project that references it.

Caller and Callee​

A callee is a Biml file that declares one or more parameter properties at the top. A caller is a Biml file that uses an Expression Control Block to invoke the callee and passes the parameter values in order. The order of parameters in the caller must match the order of property declarations in the callee.

<Packages>
  <#=CallBimlScript("Callee.biml", batchLabel, batchNumber)#>
</Packages>
<#@ property name="batchLabel" type="String" #>
<#@ property name="batchNumber" type="Int32" #>
<Package Name="<#=batchLabel#>_<#=batchNumber#>" />

Expression Control Blocks first parse their content as a string and then replace the block with that string. The callee must contain only what is valid in the caller's surrounding context. Above, the call sits inside Packages, so the callee must produce one or more Package elements.

Parameters are not limited to simple types. AstTableNode, generic List, DataTable, and other complex types are valid, which opens the door to passing whole metadata objects rather than primitive values.

Reusable Truncate and Load Pattern​

The next caller iterates over every table in a source database and asks the callee to build a truncate and load package for each one. Four parameters control the result: the table node, the source connection name, the destination connection name, and the destination schema.

<# var ingestSrc = SchemaManager.CreateConnectionNode("Ingest", "Data Source=.;Initial Catalog=OperationsDb;Provider=SQLNCLI11.1;Integrated Security=SSPI;"); #>
<# var ingestSchema = ingestSrc.GetDatabaseSchema(ImportOptions.ExcludeViews); #>

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="Ingest" ConnectionString="Data Source=.;Initial Catalog=OperationsDb;Provider=SQLNCLI11.1;Integrated Security=SSPI;" />
    <OleDbConnection Name="Landing" ConnectionString="Data Source=.;Initial Catalog=LandingDb;Provider=SQLNCLI11.1;Integrated Security=SSPI;" />
  </Connections>
  <Packages>
    <# foreach (var srcTable in ingestSchema.TableNodes) { #>
      <#=CallBimlScript("LoadPackage.biml", srcTable, "Ingest", "Landing", "land") #>
    <# } #>
  </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>

Changing the load pattern, for example to add a second derived column, requires editing only the callee file.

Toggling Logging Levels​

A logging configuration is verbose and is a strong candidate for centralization. The next callee accepts a single LogType parameter and emits different LogEvent elements based on its value. Switching from LogErrors to LogAll across every package becomes a one-line change.

<#=CallBimlScript("LoggingCallee.biml", "LogErrors")#>
<#=CallBimlScript("LoggingCallee.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 behaves like a parameterized include or a stored procedure. The callee declares parameters with property directives. The caller passes values through an Expression Control Block. The block is replaced inline by whatever the callee returns. When parameters are not needed, plain include files are simpler.