Building the Master Child Execution with BIML


In a previous post we looked at using BIML to execute a stored proc in order to assign a return value to a variable in BIDS.  The use case for this is pretty simple, when we execute a package, we want to get an ID for the execution and then record execution information against that ID.  When we execute packages through a master and child arrangement, we need to do two things.  Firstly, the master package gets an execution id (to record the batch) and secondly, each child package execution gets its own id (relative to the parent) to record its own execution.  This is the classic application of header / detail relationships as seen in the following diagram.

image

If you’d like to build this in your environment, here’s the SQL (I’ve used an ETL_FRAMEWORK schema and included some procs to return return and audit_id)

USE [EXAMPLES]
GO

/* Header Table */
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_etl_framework_master_exec] PRIMARY KEY CLUSTERED ([RUN_ID])
);

/* child table */
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_etl_framework_master_child_exec] PRIMARY KEY CLUSTERED ([CHILD_ID]) 
 );

ALTER TABLE [ETL_FRAMEWORK].[MASTER_CHILD_EXEC]  WITH CHECK ADD  CONSTRAINT [fk_etl_framework_master_child_exec_child_id] FOREIGN KEY([RUN_ID])
REFERENCES [ETL_FRAMEWORK].[MASTER_EXEC] ([RUN_ID])
ON DELETE CASCADE
;

ALTER TABLE [ETL_FRAMEWORK].[MASTER_CHILD_EXEC] CHECK CONSTRAINT [fk_etl_framework_master_child_exec_child_id]
;


/* proc for master_id */
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;

/* proc for child_id (requires master_id) */
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;

/****************************************************/
/*	Want an example of how it runs ? Try this		*/
/****************************************************/
declare @master_id int; 
declare @child_id int ;;
exec @master_id = [ETL_FRAMEWORK].[MASTER_EXEC_ID_GET] 'TEST'
print @master_id;
exec @child_id = [ETL_FRAMEWORK].[MASTER_CHILD_EXEC_ID_GET] @master_id, 'TEST CHILD';
print @child_id ;

Now to BIML

Our solution is pretty simple – the parent fires first, gaining a id (fire the proc [ETL_FRAMEWORK].[MASTER_EXEC_ID_GET] ) and then passes this variable to the child package.  In the child package, we’ve registered MASTER_AUDIT_KEY as a parameter with the execute package task passing the variable down to the child.  We can see this in the two images below;

image

image

You’ll also notice that I have 2 biml files to build the package(s), these are provided in full as;

00_CHILDREN

<#@ 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="Child01" 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>

01_MASTER

<#@ 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 Child01"  >
            <ExternalProjectPackage Package="Child01.dtsx" />
            <ParameterBindings>
              <ParameterBinding VariableName="User.MASTER_AUDIT_KEY" Name="MASTER_AUDIT_KEY" />
            </ParameterBindings>
          </ExecutePackage>
        </Tasks>
      </Package>
    </Packages>
  </Biml>

There are a few things to remember here so I’ll break each out into its own paragraph.  I’ve been over some of these points before but they are worth inclusion nevertheless.

The 2 BIML files can be executed together by selecting both, right clicking and selecting the ‘Generate SSIS Packages’ from the popup window.  The ability to do this is required because a biml snippet (for example the connection string) is defined in one code file and referenced in the other.  Naturally, the execution order is important (note the connection is defined in child) so we want to compile that (child) package first.  The use of the keyword ‘tier’ in the directive of the file determines the order of compilation for selected packages.

We can define a variable or parameter within in required scope through the tree structure of the XML document.  I’ve scoped these variables to the package (they are declared directly under the package node), however, if I wanted to restrict the scope of a variable to a container, I could declare it within the containers XML nodes.

We pass the parameter from the master package to the child through the parameter bindings tag of the ExecutePackage task.  Its pretty self explanatory (when you look at the code) but this is the focus of the post so its probably worth at least calling it out 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s