Sql Server 2012 Integration Services Master Child Pattern

Project Deployment with Sql Server 2012 Integration Services


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


The article demonstrates the new project deployment model with Sql Server data tools. A lot of effort has been put into simplifying the deployment of solutions and is one of the stand out features of Sql Server data tools. For legacy purposes you can still switch to the package deployment model, however the default is the project model. Before diving into it, lets have a quick look at where we are at.

Progress to date

Following the series, you should now have the database schema(s), load and extract packages created. All packages should be able to be successfully executed (and the results logged) within Data Services before deploying your project. The final step is deploying, and tying it altogether with environments and jobs that run on a scheduled basis

Creating the the catalog

Before executing a project deployment, an Integration Service catalog has to be created. Right clicking the new Integration Services Catalog on the database tree and selecting create catalog displays the dialog below

Create catalog data services context menu Create catalog data services dialog

Enabling the automatic execution of the stored procedure performs maintenance of the state of operations for the SSISDB catalog. It fixes the status of any packages there were running if and when the SSIS server instance goes down.

Deploying the solution

Deploying a Sql Server data tools is now as simple as right clicking and selecting deploy.

Right click deployment with Sql Server data tools

This will start the deployment wizard, which as the default settings of a project deployment with the path setting to the .ispac file of the project. For a first time deployment, a subfolder will have to be created. i.e. MasterChildCatalog

Note: .Ispac files are zipped files which contain all of the projects objects, e.g. packages, shared connection managers, project params etc.

Deployment Wizard - Select source

The next page selects the destination server and the project.

Deployment Wizard - Select destination

Finally, the wizard deploys to the server.

Deployment Wizard - Results

Once deployed, the project should look like

Deployed data services project

Creating Environments

Right clicking on the MasterChildCatalog and selecting configure allows you to con add different Environments and set their variables. Envrionments are a new feature which allow to assign different parameters depending on the instance. Typical instances may be Development, UAT and Production (although its unlikely this will be on the same server).

SSIS Project Parameters dialog SSIS Project References dialog

Apart from the Application, Destination and System parameters, all others are Environmental variables. It can be thought of as all connection information is stored in the environment variables, as these are really the only differences between environments. In this instance the development Environment has be scripted.

SSIS Development Environment variables

The dialog's above where populated with the script below (a partial snippet, the entire script is available on github)

This script:

  1. Creates the environment
  2. Creates the environment variables
  3. Creates the environment reference (within the project)
  4. Creates the parameter variables (most referencing the development environment variables)

if not exists (select * from catalog.environments where name = 'Development')

	-- create development environment
	exec catalog.create_environment
		@folder_name = 'MasterChildCatalog', 
		@environment_name = 'Development', 
		@environment_description = ''

	-- create development environment variables
	exec catalog.create_environment_variable
		@folder_name = 'MasterChildCatalog', 
		@environment_name = 'Development', 
		@variable_name = 'AdminServer', 
		@data_type =N'String', 
		@sensitive = 0, 
		@value = N'.\SQL2012', 
		@description = N''

	-- ...
	-- ...

	-- create project reference to development environment
	declare @referenceId bigint
	exec catalog.create_environment_reference
		@folder_name = 'MasterChildCatalog', 
		@project_name = 'SSISMasterChildPattern', 
		@environment_name = 'Development', 
		@reference_type = 'R', 
		@reference_id = @referenceId output

	-- configure parameters
	exec catalog.set_object_parameter_value
		@object_type = 20,		-- 20 = project, 30 = Package
		@folder_name = 'MasterChildCatalog', 
		@project_name ='SSISMasterChildPattern', 
		@parameter_name = 'AdminServer', 
		@parameter_value = 'AdminServer',
		@object_name = 'SSISMasterChildPattern', 
		@value_type = 'R'				-- R = reference(d by environmental variable), V = value type

	exec catalog.set_object_parameter_value
		@object_type = 20,
		@folder_name = 'MasterChildCatalog', 
		@project_name ='SSISMasterChildPattern', 
		@parameter_name = 'Application', 
		@parameter_value = N'',
		@object_name = 'SSISMasterChildPattern', 
		@value_type = 'V'

	-- ...
	-- ...

Creating the Extract and Load Jobs

The Extract and Load jobs are the final piece of the puzzle, which provide the discriminators for the package table. The Package tab for the Integration Services step is the same for both jobs, pointing to the master package

Integration Services job Step - Pacakge

The configuration tab determines what pacakges to run by the (bold) parameters, first we are only executing the extract packages with the Load Staging job

Then the load packages with the Load Warehouse job

Integration Services job Step - Configuration


The 2 jobs can then be configured to run independently on a scheduled basis. Running then manually should give you the magic dialog

Sql Server Job execution success dialog

And results logged in the execution table

Execution log table

If things don't work first time (generally they don't with Integration Services) there is now a comprehensive reporting suite to diagnose the problem(s) by right clicking on the project

SSIS All Executions Report context menu All Executions report

Source Code

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