Project Deployment Model in SSIS

Project Deployment Model in SSIS

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:

Project Deployment Model in SSIS

Destination – SQL Server Database

Production: ETL2017_PROD

Project Deployment Model in SSIS

QA and DEV: ETL2017_QA & ETL2017_DEV

Project Deployment Model in SSIS

 

Environment Creation in SSIS Catalog:

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

 

Project Deployment Model in SSIS

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:

 

Project Deployment Model in SSIS Project Deployment Model in SSIS

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

 

Project Deployment Model in SSIS

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:

 

Project Deployment Model in SSIS

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

Project Deployment Model in SSIS

PROD

Project Deployment Model in SSIS

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).

Project Deployment Model in SSIS

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

 

Project Deployment Model in SSIS Project Deployment Model in SSIS Project Deployment Model in SSIS

Review the given settings and Click on Deploy:

Project Deployment Model in SSIS

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

Project Deployment Model in SSIS

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:

 

Project Deployment Model in SSIS Project Deployment Model in SSIS Project Deployment Model in SSIS

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

 

Project Deployment Model in SSIS Project Deployment Model in SSIS Project Deployment Model in SSIS

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:

 

Project Deployment Model in SSIS Project Deployment Model in SSIS

Below are the DEV, QA and Production execution reports:

 

Project Deployment Model in SSIS Project Deployment Model in SSIS Project Deployment Model in SSIS Project Deployment Model in SSIS Project Deployment Model in SSIS Project Deployment Model in SSIS Project Deployment Model in SSIS

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.

 

Posted in MSBI, SSIS | Tagged , , , , , | 6 Comments
Subscribe
Notify of
guest
6 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
trackback

[…] we’ll see how we can deploy a SSIS package for multiple environments in SQL Server 2016. Here is the post for deploying SSIS package for DEV, QA and Production […]

Bhanu
Bhanu
7 years ago

Thank you Uday very good explanation.

Lokendra
Lokendra
7 years ago

Nice Step by Step Description.

trackback

[…] created in “Project Deployment Model” before that it used to be “Package Deployment Model”. Here you can see how to use Project Deployment Model to deploy […]