Sql Server 2012 Integration Services Master Child Pattern

The Master package for loading child packages in Integration Services 2012

Index

Creating a simple Master Child pattern within Sql Server 2012 Integration Services
The Master package for loading child packages in Integration Services 2012
The child extract package pattern in Integration Services 2012
The child dimension load pattern, extending the Slowly Changing Dimension
A pattern for loading fact tables with inserted, updated and deleted data
Project Deployment with Sql Server 2012 Integration Services

Introduction

This post will examine each of the Tasks in the Master package (below), the interaction with the ETL_Admin database and all the variables within the package in detail.

Master Package Control flow

Get Packages for processing (Execute SQL Task)

This task returns a resultset for a given application, system and destination. These variables passed into the stored procedure are mapped from the project params.

Editor for Execute Sql task - General Editor for Execute Sql task - Parameter Mapping

The variables passed in are just a way of logically grouping which child packages to load. The GetPackagesForProcessing procedure initialises the logging, adding an entry for each enabled package and returns the packages for processing.

alter proc dbo.GetPackagesForProcessing
	@Application varchar(100),
	@System varchar(100),
	@Destination varchar(100)
as
	begin
	set nocount on

	if (
		select	count(*) 
		from	dbo.Package 
		where	[Application] = @Application 
		and	[System]  = @System
		and	Destination = @Destination
	) = 0
	begin
		raiserror('The package collection cannot be found 
			(Application: %s, System: %s, Destination: %s)', 
			16, 1, @Application, @System, @Destination)	
	end

	declare @RunId int
	exec dbo.GetSeed 'RunId', @RunId output 

	insert	dbo.ExecutionLog(PackageId, RunId, [Status])
	select	Id, @RunId, 'Not Started'
	from	dbo.Package 
	where	[Application] = @Application 
	and	[System]  = @System
	and	Destination = @Destination
	and	LoadEnabled = 1
	order by [LoadOrder]

	select	RunId = @RunId, PackageId = Id, PackageName = Package
	from	dbo.Package 
	where	[Application] = @Application 
	and	[System]  = @System
	and	Destination = @Destination
	and	LoadEnabled = 1
	order by [LoadOrder]	

	end

The results from the above procedure are stored in the PackageList user variable (object)

Iterate Packages (Foreach Loop container)

The Iterate Packages container takes the results returned from above and iterates over them using an ADO Enumerator.

Foreach Loop Container Editor - Collection

Each iteration assigns the current record to the RunId, PackageId and PackageName variables by ordinal.

Foreach Loop Container Editor - Variable Mappings

These variables are used within the nested tasks below.

Update log status for given package (Execute SQL Task)

Updates the log to "starting" for a given packageId by calling the UpdateExecutionLog stored procedure.

Editor for Execute Sql task with Sql Statement expanded

The stored procedure has several default parameters, making easy to use within a script task, note how the Sql above only populates 4 for the procedures parameters.

alter proc dbo.UpdateExecutionLog
	@RunId int,
	@PackageId int,
	@Status varchar(20),
	@StartDate datetime = null,
	@EndDate datetime = null,
	@Message varchar(max) = '', 
	@RowsRead int = 0, 
	@RowsInserted int = 0, 
	@RowsUpdated int = 0, 
	@RowsDeleted int = 0, 
	@RowsUnchanged int = 0
as
	begin
	set nocount on


	-- use existing StartTime if not provided
	if exists(select * from dbo.ExecutionLog 
		where RunId = @RunId and PackageId = @PackageId)
		if @StartDate is null
			set @StartDate = (select StartTime 
				from dbo.ExecutionLog 
				where RunId = @RunId and PackageId = @PackageId)

	update	dbo.ExecutionLog
	set	[Status] = @Status, 
		StartTime = @StartDate,
		EndTime = @EndDate, 
		[Message] = @Message, 
		RowsRead = @RowsRead, 
		RowsInserted = @RowsInserted, 
		RowsUpdated = @RowsUpdated, 
		RowsDeleted = @RowsDeleted, 
		RowsUnchanged = @RowsUnchanged
	where
		RunId = @RunId
	and	
		PackageId = @PackageId

	end

Reset iteration variables (Execute SQL Task)

Before executing each package, the row counters (and any error messages) which eventually end up in the execution log are reset. These counters cover the CRUD operations of the Extract and Load packages (each having their own pattern). There's no call to the ETL_Admin database here, the variables are just reset to zero.

Editor for Execute Sql task - Reset Row Counters

Execute Package

The Execute Package task loads each enumerated PackageName variable which is set as an expression. There is now a Reference Type property within this task. Using the default of Project Reference, the child packages name is all that is required, as its expected that this package exists within the project. Changing this to External Reference you are presented with the 2008 options which will allow you to load child packages external to the project from either MSDB or the file system.

Execute Package Editor - Child Packages

Below shows how the package to load is assigned by the user variable (itself assigned in the iterator)

Execute Package Editor - Expressions

There is also an OnError event wired on this task

Execute Package Editor - On Error Handler

Which is responsible for assigning any child package errors to user variables with the following script

/// <summary>
/// This method is called when this script task executes in the control flow.
/// Before returning from this method, set the value of Dts.TaskResult to 
/// indicate success or failure.  To open Help, press F1.
/// </summary>
public void Main()
{
    var errDescription = Dts.Variables["System::ErrorCode"].Value.ToString();
    var errCode = Dts.Variables["System::ErrorDescription"].Value.ToString();
    var message = Dts.Variables["User::Message"].Value.ToString();

    Dts.Variables["User::Message"].Value = string.IsNullOrEmpty(message) ?
        string.Format("{0} {1}", errCode, errDescription) :
        string.Format("{2}, {0} {1}", errCode, errDescription, message);

	Dts.TaskResult = (int)ScriptResults.Success;
}

Note: With Integration Services 2012 there still doesn't seem to be an easy way of working out if a task has an event handler wired or not. Its easily missed. It would be nice if there was another badge on the task (similar to the new expressions badge) to indicate that an event has been subscribed to.

Update Audit - success (Execute SQL Task)

Updates the execution log for the given package calling the proc UpdateExecutionLog

Editor for Execute Sql task - Audit Succcess

Update Audit - failure (Execute SQL Task)

Again, updates the execution log. This time with additional error information

Editor for Execute Sql task - Audit Fail

Project Params Glossary

Param Name Description
AdminServer The server name where the ETL_Admin database resides
Application A discriminator for grouping and loading child packages
Destination A discriminator for grouping and loading child packages
DestinationInitialCataog The destination database name
DestinationServer The server name where the destination database resides
SourceInitialCataog The source database name
SourceServer The server name where the source database resides
System A discriminator for grouping and loading child packages

Package Variables Glossary

Variable Name Description
Message Error Message populated in the OnError event of the execute Package task
PackageId The Id of the package to be executed within the iteration (the database package Id, not be confused with the System packageID)
PackageList A table variable, containing the RunId, PackageId and PackageName of the packages to be executed
PackageName The current package name
RowsDeleted The number of rows deleted (Load pattern)
RowsInserted The number of rows inserted (Load pattern)
RowsRead The number of rows read and loaded (Extract pattern)
RowsUnchanged The number of rows unchanged (Load pattern)
RowsUpdated The number of rows updated (Load pattern)
RunId The current Run Id

Summary

This article gives you an in-depth look at how the master package is built and runs, using the ETL_Admin database. Download the source code (below) and have a look around. In the next post I will have an in-depth look at the first child pattern, the Extract Pattern.

Source Code

git clone https://github.com/mindfulsoftware/SSISMasterChildPattern.git