Skip to main content

Modular Table Replication with CallBimlScript

Why Modularize​

A single Biml file that walks a source database and emits one truncate and load package per table works, but the per-package logic is hard to maintain when it lives inside a foreach loop. Moving that per-package block into a separate Biml file and invoking it with parameters splits the work cleanly. The driver file owns metadata discovery and orchestration. The callee file owns one package definition. Changes to package shape touch only the callee.

The Driver File​

The driver file gathers a list of tables from the source database, declares the connections, and calls the per-package Biml file once per table.

<#@ template language="C#" hostspecific="true" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.IO" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<#
  string sourceConnString = @"Server=localhost\sql2019;Initial Catalog=RetailDb;Integrated Security=SSPI;Provider=SQLNCLI11.1";
  string targetConnString = @"Server=localhost\sql2019;Initial Catalog=RetailDb_Mirror;Integrated Security=SSPI;Provider=SQLNCLI11.1";

  string tableQuery = @"
    SELECT
      SCHEMA_NAME(t.schema_id) AS schemaName
    , t.name AS tableName
    FROM sys.tables AS t
    WHERE t.is_ms_shipped = 0
      AND t.name <> 'sysdiagrams';
  ";

  DataTable tableList = ExternalDataAccess.GetDataTable(sourceConnString, tableQuery);
#>
  <Connections>
    <OleDbConnection
      Name="SRC"
      CreateInProject="false"
      ConnectionString="<#= sourceConnString #>"
      RetainSameConnection="false">
    </OleDbConnection>
    <OleDbConnection
      Name="DST"
      CreateInProject="false"
      ConnectionString="<#= targetConnString #>"
      RetainSameConnection="false">
    </OleDbConnection>
  </Connections>

  <Packages>
    <# foreach (DataRow row in tableList.Rows) { #>
      <#=CallBimlScript("PackageTemplate.biml", row[0].ToString(), row[1].ToString())#>
    <# } #>
  </Packages>
</Biml>

The driver fetches a DataTable of schema and table names, sets up the source and destination connections, and then iterates the rows. For each row it calls the package template, passing column 0 (schema) and column 1 (table) as parameters.

The Package Template​

The package template declares two property values that match the parameters from the caller. Anywhere the original code referenced row column zero or one, the template now references the named parameters.

<#@ property name="schema" type="String" #>
<#@ property name="table" type="String" #>

<Package ConstraintMode="Linear"
         Name="<#=schema#>_<#=table#>">
  <Variables>
    <Variable Name="SchemaName" DataType="String"><#=schema#></Variable>
    <Variable Name="TableName" DataType="String"><#=table#></Variable>
    <Variable Name="QualifiedTableSchema"
              DataType="String"
              EvaluateAsExpression="true">"[" + @[User::SchemaName] + "].[" + @[User::TableName] + "]"</Variable>
  </Variables>
  <Tasks>
    <Dataflow Name="DFT">
      <Transformations>
        <OleDbSource
          Name="OLE_SRC <#=schema#>_<#=table#>"
          ConnectionName="SRC">
          <TableFromVariableInput VariableName="User.QualifiedTableSchema" />
        </OleDbSource>
        <OleDbDestination
          Name="OLE_DST <#=schema#>_<#=table#>"
          ConnectionName="DST"
          KeepIdentity="true"
          TableLock="true"
          UseFastLoadIfAvailable="true"
          KeepNulls="true">
          <TableFromVariableOutput VariableName="User.QualifiedTableSchema" />
        </OleDbDestination>
      </Transformations>
    </Dataflow>
  </Tasks>
</Package>

Right clicking the driver and generating SSIS Packages produces one package per table, all built from a single template. The pattern extends to any per-row package generator and is available in the free Biml engine.