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
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
and can make use of 3 different databases
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
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
You can see in the package Connection Manager pane, that project level connection managers are labelled as such.
Taking a leaf out of BIDS Helper 2008 expressions on objects are now highlighted with an fx badge.
Params can be thought of as readonly variables which can be injected at the Project level (via environment variables) or Package level
To distinguish them against variables they are prefixed with a $ sign.
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.
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 this the entry point to the project and is responsible for the loading, execution and logging of the child packages.
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 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)
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)
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 used by the master package are
|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|
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.
git clone https://github.com/mindfulsoftware/SSISMasterChildPattern.git