Sql Server 2012 Integration Services Master Child Pattern

Creating a simple Master Child pattern within Sql Server 2012 Integration Services

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

Managing ETL processes can easily get out of hand, packages can be authored differently not adhering to any pattern or standard. This makes them difficult to manage and maintain. Using this master child pattern, you'll be able to group packages by application, run and audit their success or failure.

The pattern uses 3 different package templates

  • The master package (runs and audits the child packages)
  • The child Extract pacakge (extracts the data from the source into a staging database)
  • The child Load package (transforms and loads the data into the warehouse)

and can make use of 3 different databases

  • A Admin database (contains all package config and log table)
  • A Staging database (contains the raw data extracted from the source)
  • A Warehouse database

Disclaimer: This series is about ETL design, not warehouse design. It will demonstrate the loading of dimension and fact tables into a warehouse, but will not be a complete warehouse solution. The warehouse schema will be limited to accommodate the package design only.

Before we start, a (very) quick tour of the new features in Integration Services 2012 is in order. The first thing you'll notice is the rename from SQL Server Business Intelligence Development Studio to Sql Server Data Tools

A Quick tour of the new features in Integration Services 2012

Project Level Connection Managers

Project level connection Managers are exactly that! Instead of creating the same connection manager over and over for each child package, the can now be created at the project level. A real time save for both package creation and maintanance

Connection Managers in Solution Tree

You can see in the package Connection Manager pane, that project level connection managers are labelled as such.

Connection Managers in package Connection Manager pane

Expression badges on objects

Taking a leaf out of BIDS Helper 2008 expressions on objects are now highlighted with an fx badge.

Expression badge on an execute package task

Project and Package Params

Params can be thought of as readonly variables which can be injected at the Project level (via environment variables) or Package level

Project level parameters Project param used in an expression

To distinguish them against variables they are prefixed with a $ sign.

Integration Service Catalogs

Using the file system or msdb is now considered a Legacy package deployment model as opposed to the new project deployment model where projects are stored in an SSISDB catalog. There is no longer any reason to connect to the Integration Services engine directly. All tasks can be completed within the database.

Integration Services Catalog Tree View

Environments

Within each project you now can have multiple environments e.g. development, UAT and production in reality though, these environment would never be on the same server.

I will go through a deployment in detail in later posts, look forward to deploying without ugly xml config files.

The Master Package (A high Level overview)

The master package this the entry point to the project and is responsible for the loading, execution and logging of the child packages.

Master Package Control flow

The first task is to load the child packages to be processed into a table. This is then iterated over with a for each loop container. Each iteration logs the beginning and the final result of each package loaded and executed. To facilitate this, the Admin database provides all the configuration and logging of the master package.

The Admin database

The admin database is primarily responsible for managing child packages which are logically grouped together. It also contains a package execution log which was essential in 2008 (the reporting in 2012 is excellent)

Master child admin database schema

The Package table

Most of the fields in the package table are self explanatory apart from the first 3. The Application, System and Destination fields are a way of logically grouping the packages, which will eventually be run from a scheduled job. It can be thought of as...

For the ApplicationName I will be sourcing the data from SystemName and loading it into DestinationName

for this series this translates to

For the Demo I will be sourcing the data from Northwind and loading it into Staging

Its important to note that these names are just discriminators for executing a collection of packages, they do not have to be database names (they are not used in connection managers)

The PackageVariable table

Each child package may required specific variables for execution, typical examples may be a data range or a currency code. Variables in this table are loaded and mapped into package variables (not parameters).

The stored procedures (called by the master package)

The stored procedures used by the master package are

Procedure name Description
GetPackagesForProcessing Initialises the Execution log and returns the packages for a given Application, System and Destination
GetPackageVariables Returns the package variables for a given packageId
GetSeed Returns a unique seed value (used to populate the RunId of the execution log
UpdateExecutionLog Updates the execution log with package processing progress

Summary

This should give a high level view of where we are headed. There's some really nice new features in Integration Services 2012. The next post will deep dive into the master package, but if you want to have a look around yourself, the source code is below.

Source Code

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