Copy Data Dynamically with BimlScript

Why This Pattern Matters

Copying every table in a database to another database is a common task. The Transfer SQL Server Objects task has known limitations, the Import and Export Wizard does not give full control of the resulting package, and hand-building one package per table is repetitive. BimlScript can iterate over the source metadata and emit one package per table, fully controlling the resulting SSIS without the rote work.

A Package per Source Table

The script below pulls the list of tables from a source database and emits one extract package per table. Each package contains a single dataflow that selects every row from the source and writes to the matching destination table.

<#@ template language="C#" hostspecific="true" #>
<#@ import namespace="System.Data" #>

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="SourceConn" ConnectionString="Provider=SQLNCLI11;Server=.;Initial Catalog=SalesOpsDB;Integrated Security=SSPI;" />
    <OleDbConnection Name="TargetConn" ConnectionString="Provider=SQLNCLI11;Server=.;Initial Catalog=ReportingDB;Integrated Security=SSPI;" />
  </Connections>
  <Packages>
    <#
      string metadataConnectionString = "Provider=SQLNCLI11;Server=.;Initial Catalog=SalesOpsDB;Integrated Security=SSPI;";
      DataTable tables = ExternalDataAccess.GetDataTable(metadataConnectionString,
        "SELECT '[' + s.name + '].[' + t.name + ']' FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id");
      foreach (DataRow row in tables.Rows)
      {
    #>
    <Package Name="Extract <#=row[0]#>" ConstraintMode="Linear" AutoCreateConfigurationsType="None">
      <Tasks>
        <Dataflow Name="Copy Data">
          <Transformations>
            <OleDbSource Name="Retrieve Data" ConnectionName="SourceConn">
              <DirectInput>SELECT * FROM <#=row[0]#></DirectInput>
            </OleDbSource>
            <OleDbDestination Name="Insert Data" ConnectionName="TargetConn">
              <ExternalTableOutput Table="<#=row[0]#>" />
            </OleDbDestination>
          </Transformations>
        </Dataflow>
      </Tasks>
    </Package>
    <# } #>
  </Packages>
</Biml>

The script does not create the destination tables. The simplest setup is to script the source database's structure and recreate it under the target name. The 'sys.tables' query supplies the schema-qualified table names that drive the loop.

Adding Per-Table WHERE Clauses

A common refinement is to filter some tables instead of copying every row. A small metadata table in the destination database can hold the optional WHERE clauses.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="TargetConn" ConnectionString="Provider=SQLNCLI11;Server=.;Initial Catalog=ReportingDB;Integrated Security=SSPI;" />
  </Connections>
  <Tables>
    <Table Name="ExtractFilter" ConnectionName="TargetConn">
      <Columns>
        <Column Name="TableName" DataType="String" Length="255" />
        <Column Name="WhereSql" DataType="String" Length="4000" />
      </Columns>
    </Table>
  </Tables>
</Biml>

Populate the table with the filters needed. For example:

TableName	WhereSql
[dbo].[OrderLineItem]	WHERE [OrderAmount] >= 1000
[dbo].[ChannelSales]	WHERE [OrderAmount] >= 1000

The Combined Script

The final script reads the filter table, then looks up the matching row by table name when emitting each source's 'DirectInput'. Tables with no matching row produce a plain 'SELECT *'; tables with a row append the WHERE clause.

<#@ template language="C#" hostspecific="true" #>
<#@ import namespace="System.Data" #>

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="SourceConn" ConnectionString="Provider=SQLNCLI11;Server=.;Initial Catalog=SalesOpsDB;Integrated Security=SSPI;" />
    <OleDbConnection Name="TargetConn" ConnectionString="Provider=SQLNCLI11;Server=.;Initial Catalog=ReportingDB;Integrated Security=SSPI;" />
  </Connections>
  <Packages>
    <#
      string targetConnectionString = "Provider=SQLNCLI11;Server=.;Initial Catalog=ReportingDB;Integrated Security=SSPI;";
      DataTable whereClauses = ExternalDataAccess.GetDataTable(targetConnectionString,
        "SELECT TableName, WhereSql FROM ExtractFilter");

      string metadataConnectionString = "Provider=SQLNCLI11;Server=.;Initial Catalog=SalesOpsDB;Integrated Security=SSPI;";
      DataTable tables = ExternalDataAccess.GetDataTable(metadataConnectionString,
        "SELECT '[' + s.name + '].[' + t.name + ']' FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id");
      foreach (DataRow row in tables.Rows)
      {
    #>
    <Package Name="Extract <#=row[0]#>" ConstraintMode="Linear" AutoCreateConfigurationsType="None">
      <Tasks>
        <Dataflow Name="Copy Data">
          <Transformations>
            <OleDbSource Name="Retrieve Data" ConnectionName="SourceConn">
              <#
                var dataRow = whereClauses.Select(string.Format("TableName = '{0}'", row[0]));
                string whereSql = dataRow.Length == 0 ? string.Empty : dataRow[0][1].ToString();
                string sql = string.Format("SELECT * FROM {0} {1}", row[0], whereSql);
              #>
              <DirectInput><#=sql#></DirectInput>
            </OleDbSource>
            <OleDbDestination Name="Insert Data" ConnectionName="TargetConn">
              <ExternalTableOutput Table="<#=row[0]#>" />
            </OleDbDestination>
          </Transformations>
        </Dataflow>
      </Tasks>
    </Package>
    <# } #>
  </Packages>
</Biml>

Right-clicking the Biml file and choosing Expand produces one package per source table. The data flow copies rows from 'SourceConn' to 'TargetConn', applying any WHERE clauses recorded in the 'ExtractFilter' table.

Where to Take It Next

The same template can grow to recreate destination tables, truncate before load, or skip tables based on additional metadata. The point is to drive the SSIS output from data instead of writing dozens or hundreds of nearly identical packages by hand.