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.
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
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
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 a Sql Server data tools is now as simple as right clicking and selecting deploy.
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.
The next page selects the destination server and the project.
Finally, the wizard deploys to the server.
Once deployed, the project should look like
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).
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.
The dialog's above where populated with the script below (a partial snippet, the entire script is available on github)
use SSISDB go if not exists (select * from catalog.environments where name = 'Development') begin -- 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' -- ... -- ...
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
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
The 2 jobs can then be configured to run independently on a scheduled basis. Running then manually should give you the magic dialog
And results logged in the execution 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
git clone https://github.com/mindfulsoftware/SSISMasterChildPattern.git