Skip to main content

Bulk Changes With XML Templates in Biml

Why Use a Template File​

A previous walkthrough showed how to apply uniform settings (such as DefaultBufferMaxRows) to every package by walking the Biml object model in code. The same approach scales to columns, events, and other child objects, but at some point hand authoring those child objects in code rather than Biml XML becomes painful. BimlStudio solves this with transformers; BimlExpress does not have transformers, but a template file plus a high tier merge file gets close to the same effect.

Step 1: Some Static Biml to Apply Templates To​

To keep the example focused on the template mechanism, the project starts with a connection, a database, a schema, two tables, and two empty packages:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="StgTarget"
      ConnectionString="Provider=SQLNCLI11;Server=localhost;Initial Catalog=StagingDb;Integrated Security=SSPI;" />
  </Connections>
  <Databases>
    <Database Name="StagingDb" ConnectionName="StgTarget" />
  </Databases>
  <Schemas>
    <Schema Name="dbo" DatabaseName="StagingDb" />
  </Schemas>
  <Tables>
    <Table Name="StgEntityA" SchemaName="StagingDb.dbo">
      <Columns>
        <Column Name="ColA1" DataType="String" Length="50" />
        <Column Name="ColA2" DataType="String" Length="50" />
      </Columns>
    </Table>
    <Table Name="StgEntityB" SchemaName="StagingDb.dbo">
      <Columns>
        <Column Name="ColB1" DataType="String" Length="50" />
        <Column Name="ColB2" DataType="String" Length="50" />
      </Columns>
    </Table>
  </Tables>
  <Packages>
    <Package Name="LoadEntityA" />
    <Package Name="LoadEntityB" />
  </Packages>
</Biml>

Step 2: Define a Template Table and a Template Package​

The next file declares one TemplateTable carrying a single audit column and one TemplatePackage carrying an OnPostExecute event. The merge step in step three propagates these to every non template object. The downside of this approach: only objects that are already declared in the destination (Tables, Packages, and so on) can receive template content. Adding a TemplateDataflow expecting it to graft onto every package will not work.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Tables>
    <Table Name="TemplateTable" SchemaName="StagingDb.dbo">
      <Columns>
        <Column Name="LoadTimestamp" DataType="DateTime" />
      </Columns>
    </Table>
  </Tables>
  <Packages>
    <Package Name="TemplatePackage">
      <Events>
        <Event Name="OnPostExecuteAudit" EventType="OnPostExecute">
          <Tasks>
            <ExecuteSQL Name="PostExecute Audit" ConnectionName="StgTarget">
              <DirectInput>
                <!-- Audit SQL goes here -->
              </DirectInput>
            </ExecuteSQL>
          </Tasks>
        </Event>
      </Events>
    </Package>
  </Packages>
</Biml>

Step 3: Merge the Templates Into Every Other Object​

The merge file runs at a high tier so it sees every Biml object emitted by the earlier files. It walks every table that is not the TemplateTable, copies any missing template columns onto it, then does the same for packages and template events:

<#@ template language="VB" optionexplicit="False" tier="999" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <#
    for each tbl in RootNode.Tables.Where(Function(c) c.Name <> "TemplateTable")
      for each col in RootNode.Tables("TemplateTable").Columns
        if tbl.Columns.Where(Function(c) c.Name = col.Name).Count = 0 then
          tbl.Columns.Add(col)
        end if
      next
    next

    for each pack in RootNode.Packages.Where(Function(c) c.Name <> "TemplatePackage")
      for each eve in RootNode.Packages("TemplatePackage").Events
        if pack.Events.Where(Function(c) c.Name = eve.Name).Count = 0 then
          pack.Events.Add(eve)
        end if
      next
    next
  #>
</Biml>

After this file runs, every non template package has the audit event from the template, and every non template table has the timestamp column. To extend the pattern to other properties or child objects, add another iteration that copies the relevant property or collection.