This post helps you to understand the “Project deployment model in SSIS”. We have already created a SSIS package and environment is ready to deploy. Here are the ETL requirement and package details. We are going to deploy the package into SQL Server 2016 instance and use the same package to run for QA, TEST and PROD environment.

Environment Setup

Sample ETL Package

Source – Flat Files

We have created three different folders and customer flat files for DEV, QA and PROD as below:

Destination – SQL Server Database

Production: ETL2017_PROD

QA and DEV: ETL2017_QA & ETL2017_DEV

Environment Creation in SSIS Catalog:

We have already created a folder “XYZ_Product” in SSIS catalog as below:

From the above image you can observe that currently there are no projects deployed under the folder “XYZ_Product”. Since we need to use the same package for different environments first we need to setup the environment as below:

Same way create new environment for QA and PRODUCTION, after creating these the environment looks like below:

We have environments ready. Now we need to setup environment variables to map it with project parameters. That means we need to give input values for Server, Database, Table, User, Password, FlatFilePath. We will create these variables for QA, DEV and PROD environments. Variable names should be same and the values we can input based on the environment. Open environment “DEVELOPMENT” and input the variables as below:

If you observe we also have sensitive option which we can use it to handle sensitive data. We are using it for password. Setup variables for QA and PRODUCTION as well:

QA

PROD

From the above image you can see that we used the same variables as DEVELOPMENT but different values as per QA & PROD. We have completed environment setup. Now we can deploy the project.

Deploying SSIS Project using Project Deployment Model

Go back to SQL Server Data Tools, right click on project build and deploy (We can also deploy directly from SSIS catalogs).

Input Server connection string and select the catalog folder (XYZ_Product):

Review the given settings and Click on Deploy:

Now the project deployment is done and you can see the project and package available in Integration Catalog folder “XYZ_Product” as below:

Mapping SSIS Project to DEV, QA and PRODUCTION Environments

We have ready environment setup and we successfully deployed the project. Now we need to map the project with environment. Configure the project XYZ_Sales:

We have mapped environment to project. Now we need to map Project Parameters with Environment variables:

Executing SSIS package for multiple environments using project deployment model

Now we’ll execute the same package for QA, DEV and Production with the respective connections strings and other parameters:

Below are the DEV, QA and Production execution reports:

Summary:

• This is a sample project that explains how we can deploy SSIS package using Project Deployment Model.
• Project parameters plays vital role and allows users to control the package execution by passing the parameter values dynamically at execution time.
• To understand project deployment model we should need to focus on “Integration Service Catalogs”, “Environment Creation”, “Project Deployment”, “Project Parameters”, “Environment Variables”, “Parameterising Connection Managers”
• We are using SQL Server 2016 Developer Edition User Interface. We can also use T-SQL script to handle the project deployment and execution.