Master Child Package Execution in Biml

Why This Pattern Matters

A common audit requirement for ETL is a header and detail record per execution. The master package writes one header row and gets back a run id. Each child package writes a detail row that references that run id and gets back its own child id. The pattern lets every row, log entry, and error message in the run be tied back to the batch that produced it.

This walkthrough builds the pattern with two Biml files: one that defines the child packages and one that defines the master.

The Audit Schema

The framework needs a header table, a child table, and two stored procedures that insert a row and return the new identity value.

CREATE TABLE [ETL_FRAMEWORK].[MASTER_EXEC] (
  [RUN_ID]    int IDENTITY(1,1) NOT NULL,
  [JOB_NAME]  nvarchar(256) NULL,
  [START_DT]  datetime NULL DEFAULT (GETDATE()),
  CONSTRAINT [pk_master_exec] PRIMARY KEY CLUSTERED ([RUN_ID])
);

CREATE TABLE [ETL_FRAMEWORK].[MASTER_CHILD_EXEC] (
  [RUN_ID]    int NULL,
  [CHILD_ID]  int IDENTITY(1,1) NOT NULL,
  [JOB_NAME]  nvarchar(256) NULL,
  [START_DT]  datetime NULL DEFAULT (GETDATE()),
  CONSTRAINT [pk_master_child_exec] PRIMARY KEY CLUSTERED ([CHILD_ID]),
  CONSTRAINT [fk_master_child_exec_run] FOREIGN KEY ([RUN_ID])
    REFERENCES [ETL_FRAMEWORK].[MASTER_EXEC] ([RUN_ID]) ON DELETE CASCADE
);

CREATE PROC [ETL_FRAMEWORK].[MASTER_EXEC_ID_GET] (@JOB_NAME nvarchar(256))
AS
BEGIN
  SET NOCOUNT ON;
  INSERT INTO ETL_FRAMEWORK.MASTER_EXEC (JOB_NAME) VALUES (@JOB_NAME);
  RETURN IDENT_CURRENT('ETL_FRAMEWORK.MASTER_EXEC');
END;

CREATE PROC [ETL_FRAMEWORK].[MASTER_CHILD_EXEC_ID_GET]
  (@RUN_ID int, @JOB_NAME nvarchar(256))
AS
BEGIN
  SET NOCOUNT ON;
  INSERT INTO ETL_FRAMEWORK.MASTER_CHILD_EXEC (RUN_ID, JOB_NAME) VALUES (@RUN_ID, @JOB_NAME);
  RETURN IDENT_CURRENT('ETL_FRAMEWORK.MASTER_CHILD_EXEC');
END;

A quick smoke test confirms the procedures return the new identity values:

DECLARE @master_id int, @child_id int;
EXEC @master_id = [ETL_FRAMEWORK].[MASTER_EXEC_ID_GET] 'NIGHTLY';
EXEC @child_id  = [ETL_FRAMEWORK].[MASTER_CHILD_EXEC_ID_GET] @master_id, 'CUSTOMER LOAD';

The Child Package

The child Biml file is at tier 1 and defines the project connection plus the child package. The package exposes a 'MASTER_AUDIT_KEY' parameter so the master can pass the run id down. An Execute SQL Task captures a return value into 'CHILD_AUDIT_KEY' and a passes 'MASTER_AUDIT_KEY' and 'System.PackageName' as inputs.

<#@ template language="C#" tier="1" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="MetaData"
      ConnectionString="Provider=SQLOLEDB;Data Source=localhost\SQL2014;Integrated Security=SSPI;Initial Catalog=Examples" />
  </Connections>
  <Packages>
    <Package Name="LoadCustomer" ConstraintMode="Linear"
             ProtectionLevel="EncryptSensitiveWithUserKey">
      <Parameters>
        <Parameter DataType="Int32" Name="MASTER_AUDIT_KEY">-1</Parameter>
      </Parameters>
      <Variables>
        <Variable DataType="Int32" Name="CHILD_AUDIT_KEY">-1</Variable>
      </Variables>
      <Tasks>
        <ExecuteSQL Name="CHILD_KEY_GET" ConnectionName="MetaData">
          <DirectInput>EXEC ? = [ETL_FRAMEWORK].[MASTER_CHILD_EXEC_ID_GET] ?, ?</DirectInput>
          <Parameters>
            <Parameter VariableName="User.CHILD_AUDIT_KEY" Name="0" DataType="Int32" Direction="ReturnValue" />
            <Parameter VariableName="MASTER_AUDIT_KEY"     Name="1" DataType="Int32" Direction="Input" />
            <Parameter VariableName="System.PackageName"   Name="2" DataType="String" Length="50" Direction="Input" />
          </Parameters>
        </ExecuteSQL>
      </Tasks>
    </Package>
  </Packages>
</Biml>

The Master Package

The master Biml file is at tier 2 so the connection defined in the child file is already in scope when it compiles. The master gets its own run id, then runs the child package and binds 'User.MASTER_AUDIT_KEY' to the child parameter through 'ParameterBindings'.

<#@ template language="C#" tier="2" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <Package Name="Master" ConstraintMode="Linear"
             ProtectionLevel="EncryptSensitiveWithUserKey">
      <Variables>
        <Variable DataType="Int32" Name="MASTER_AUDIT_KEY">-1</Variable>
      </Variables>
      <Tasks>
        <ExecuteSQL Name="MASTER_KEY_GET" ConnectionName="MetaData">
          <DirectInput>EXEC ? = [ETL_FRAMEWORK].[MASTER_EXEC_ID_GET] ?</DirectInput>
          <Parameters>
            <Parameter VariableName="User.MASTER_AUDIT_KEY" Name="0" DataType="Int32" Direction="ReturnValue" />
            <Parameter VariableName="System.PackageName"    Name="1" DataType="String" Length="50" Direction="Input" />
          </Parameters>
        </ExecuteSQL>
        <ExecutePackage Name="Exec LoadCustomer">
          <ExternalProjectPackage Package="LoadCustomer.dtsx" />
          <ParameterBindings>
            <ParameterBinding VariableName="User.MASTER_AUDIT_KEY" Name="MASTER_AUDIT_KEY" />
          </ParameterBindings>
        </ExecutePackage>
      </Tasks>
    </Package>
  </Packages>
</Biml>

Compile Order and Scope

Both Biml files have to be selected together when generating SSIS packages so the connection defined in the child file is visible to the master file. The 'tier' value on each file controls the compile order: child at tier 1 compiles first because the master at tier 2 depends on its connection.

Variables and parameters live in the scope of the XML node they sit under. Declaring 'CHILD_AUDIT_KEY' inside the package makes it package scoped. Moving it inside a container would scope it to that container only.

The 'ParameterBindings' element on 'ExecutePackage' is what wires the master variable into the child parameter. Once the binding is in place every child execution carries the master run id forward, and every audit row written by the child references the run id of the batch that produced it.