Deploying SSIS Projects to Different Environments

In this post I’m explaining how you can deploy a developed SSIS project to several different environments. It might have happened to you that there are several environments that you need to deploy the SSIS projects to. Assume that you have DEV, QA, UAT and PROD environments. Some organisations might have even more environments. Also, there are many cases that you might have several PRODs that the SSIS packages should be deployed to all of them. So the scenario is that whenever you create a new SSIS project in DEV area or you may modify the existing projects, you need to deploy each SSIS project to QA for testing purposes. So, if you have 3 new SSIS projects or you’ve just modified 3 existing projects, you’ll need to deploy each project separately. It is the same story for QA guys after finishing the test cases and after the SSIS projects pass all the test cases. They’ll need to deploy all projects to UAT. Again it is the same story with UAT and PROD. It is getting harder when you need to deploy all the projects in several different PROD environments.

Using the solution below, you can easily deploy all SSIS projects from an environment to another environment or even several different environments.

Let’s start taking about the solution.

  1. Define the following parameters:
Name Data type Value Description
DSList_str String UATSRV01\SQL, UATSRV01, UATSRV03\SQL It contains destination server names in a comma delimited format
SS String DEVPC01\SQL1 It contains source server name
  1. Define the following variables:
Name Data type Value Description
DS String It contains individual destination server name
DSList Object A list of destination server names
Folder String Contains SSIS Catalog folders
OBJ Object Contains all SSIS Catalog folders and projects
Project String Contains SSIS Catalog projects
SQL String T-SQL commands

SSIS Projects 01

  1. Put a script task on the control flow area and name it “Server Names”

  2. Double click on the script task and make the changes below:

a. select “Microsoft Visual C# 2010” as the ScriptLanguage

b. On ReadOnlyVairables select “$Package::DSList_str” from the list

c. On ReadWriteVariables select “User::DSList” from the list

SSIS Projects 02

d. Click on “Edit Script…” button

e. Put the following scripts in the main() method

string array = Dts.Variables[“$Package::DSList_str”].Value.ToString();

System.Collections.ArrayList list = new System.Collections.ArrayList();

list.AddRange(array.Split(new char[] { ‘,’ }));

Dts.Variables[“User::DSList”].Value = list;

Dts.TaskResult = (int)ScriptResults.Success;

 SSIS Projects 03

f. Build and save and close the Visual Studio window

g. Click OK on the script task editor

  1. Right click on “Connection Managers” area and:

a. Define a new OLEDB connection manager. Name the connection “Source”

       i. Click on the “Source” connection manager and press F4 to navigate to connection manager’s properties. Go to “Expressions” and click on the ellipsis button.

      ii. Select ServerName from the property list and click on the ellipsis button and drag and drop the “@[$Package::SS]” parameter to expression area.

SSIS Projects 04

       iii. Click OK and OK again

b. Create another OLEDB connection manager in the same way, name it “Target”. The only thing that is different is that you need to put “@[User::DS]” variable on the ServerName property when you’re defining the expression.

  1. Put a Foreach Loop Container on the Control Flow. Name it “Repeat for all DSs” and make the changes below:

a. Double click on the foreach loop container, go to Collection section and change the Enumerator to “Foreach From Variable Enumerator”

b. From “Enumerator configuration” sections select “User::DSList” variable

SSIS Projects 05

c. Go to Variable Mappings, select “User::DS” with “0” on the index

 SSIS Projects 06

d. Click OK

  1. Connect “Server Names” to “Repeat for all DSs”

  2. Drop an Execute SQL Task on the “Repeat for all DSs” container. Name it “Collect Catalog Data”.

  3. Double click on the Collect Catalog Data and make the following changes:

a. ResultSet: Full result set

b. Connection: Source

c. SQLSourceType: Direct input

d. SQLStatement: select p.name ProjectName, f.name FolderName from [SSISDB].[catalog].projects p join [SSISDB].[catalog].folders f on f.folder_id=p.folder_id

SSIS Projects 07

e. Go to “Result Set” and put “0” under result set and select User::OBJ from the variable list

SSIS Projects 08

f. Click OK

  1. Drop another foreach loop container on the previous foreach loop container and name it “Repeat for all Folders and Projects”

Double click on the “Repeat for all Folders and Projects” and make the following changes:

i. Go to collection section and change the enumerator to “Foreach ADO Enumerator”

ii. From Enumerator configuration select “User::OBJ”

iii. Enumeration mode: Rows in the first table

SSIS Projects 09

iv. Go to Variable Mappings section and put the following settings:

Variable Index
User::Project 0
User::Folder 1

SSIS Projects 10

  1. Connect “Collect Catalog Data” to “Repeat for all Folders and Projects”

  2. Put an Expression Task on the “Repeat for all Folders and Projects” foreach loop container and name it “Create Folders Expression”

Double click on the expression task and put the following code in the expression section and click evaluate expression and then click OK:

@[User::SQL]=”Declare @folder_id bigint

if not exists (select 0 from [SSISDB].[catalog].[folders] where name = N'”+ @[User::Folder] +”‘)

EXEC [SSISDB].[catalog].[create_folder] @folder_name=N'”+ @[User::Folder] +”‘, @folder_id=@folder_id “

SSIS Projects 11

  1. Drop an Execute SQL Task on the “Repeat for all Folders and Projects” and name it “Execute Create Folders Expression”

Double click on the “Execute Create Folders Expression” and make the following changes then click OK:

i. In General section, Connection: Target

ii. SQLSourceType: Variable

iii. SourceVariable: User::SQL

  1. Connect “Create Folders Expression” to “Execute Create Folders Expression”

  2. Drop an execute process task on the “Repeat for all Folders and Projects” and name it “Project Deployment”

  3. Double click on the “Project Deployment” and make the following changes:

i. Go to process, Executable: %windir%\system32\cmd.exe

ii. Go to Expressions and click on “Expressions” from the right pane and the click on the ellipsis button. The select “Arguments” from Property and enter the following expression in the expression area:

“/C ISDeploymentWizard /S /ST:Server /SS:”+ @[$Package::SS] + ” /SP:\”/SSISDB/”+ @[User::Folder] +”/”+@[User::Project]+”\” /DS:”+ @[User::DS] +” /DP:\”/SSISDB/”+ @[User::Folder] +”/”+@[User::Project]+”\””

iii. Click OK and the OK

  1. You should see something like this

SSIS Projects 12

  1. Press F5 to execute the package

SSIS Projects 13

  1. We are done!

How it works

First of all we are converting the destination servers from comma delimited string to a table shaped list. This is done by using the “Server Names” script task. So we are converting the server names from “UATSRV01\SQL, UATSRV01, UATSRV03\SQL” to

UATSRV01\SQL
UATSRV01
UATSRV03\SQL

This list is getting passed to the “Repeat for all DSs” foreach loop container, so that the container repeats all of its subsets for each of the above list’s members.

When the “Repeat for all DSs” foreach loop container starts working it passes the server names to the “Target” connection manager, so that it can connect to the destination servers one by one.

The “Collect Catalog Data” execute SQL task connects to the source server and collects the needed data from the source SSISDB. If you run the SQL script that we have used in this execute SQL task and run it on SSMS when connected to the source server you’ll see a result set like below:

ProjectName FolderName
TransferSourceDBs QA Demo
TransformPhase QA Demo
TransferSourceDBs UAT01
TransformDWDimCustomer UAT01
PopulateDWFactSails UAT02

The above result set is stored in the “User::OBJ” variable. This variable is used as an input for the second foreach loop container named “Repeat for all Folders and Projects”. We have mapped the “User::Folder” to the second column of the above table and “User::Project” to the first column. So the “User::Folder” variable contains the source SSIS Catalog folders and the “User::Project” variable contains the source SSIS Catalog project names under the SSIS Catalog folders.

Hence, the “Execute Create Folders Expression” execute SQL task creates the folders in the destination SSIS Catalog using the same folder names exists in the source SSIS Catalog.

So far we have created the same folder as the source SSIS Catalog in the destination SSIS Catalog. Now the “Project Deployment” execute process task will deploy the projects under each folder from the source SSIS Catalog to the destination SSIS Catalog. To do so, we are running the ISDeploymentWizard command in the windows console (CMD.EXE).

Analysing the expression used in the “Project Deployment”:

“/C ISDeploymentWizard /S /ST:Server /SS:”+ @[$Package::SS] + ” /SP:\”/SSISDB/”+ @[User::Folder] +”/”+@[User::Project]+”\” /DS:”+ @[User::DS] +” /DP:\”/SSISDB/”+ @[User::Folder] +”/”+@[User::Project]+”\””

· /C means we are passing a command to cmd.exe

· ISDeploymentWizard is loading the SSIS Deployment Wizard tool

· /S forces the ISDeploymentWizard to run in silent mode

· /ST:Server represents source type. In this case that we are deploying the SSIS projects to SSIS Catalog we put Server after the colon (:) sign.

· /SS: represents Source Server which is your source SQL Server instance name. For instance DEVPC01\SQL1

· /SP: represents the source path of the SSIS Catalog that should be something like /SSISDB/{Folder Name}/{Project Name}

· /DS: represents destination server that is the destination SQL server instance name

· /DP: represents destination path which is the path of deployment project

As you can see in the above expression the source and destination folders and their underlying projects are the identical. So we will have exactly the same folders and projects in our source environment and the destination environments.

2 thoughts on “Deploying SSIS Projects to Different Environments”

  1. Quite clever but…curious why you would create something so complex which appears to just allow you to embed connections into a single parameter and then parse them. Maybe I’m missing something, but this seems overly complicated way to solve a problem I’m not aware is really a problem in the 2012 version of SSIS. Isn’t creating a separate parameter more intuitive and thus easier to maintain? I don’t view maintaining parameter values for different environments as an issue – SSDT/VS already provides for this in a very effective manner without custom coding by merely referencing parameters in source/target connection expressions. You can control parameter values when deploying through the use of VS environments it supports which automatically substitutes values for your parameters without writing any custom code.

    1. Hi gmelhaff.
      Thanks for your comments.
      Please note that the article is talking about “SSIS PROJECT DEPLOYMENT MODEL”.
      I’d like to say that setting up some environment variables in SSDT/VS won’t solve the problem. Environments are simply containers for server variables.
      In addition, we are talking about deploying the SSIS packages from an SSIS catalog to some other SSIS catalogs in different environments like QA, UAT or Live.
      So, you should setup all the things related to your SSIS project in a DEV environment before deployment. This includes defining environments, parameters, connection string etc..
      After you completed the development, you need to deploy the whole project to an SSIS catalog hosted in DEV environment using SSDT. So, you’re deploying the whole project including environments variables.
      Then, you can use the above solution to build a solution to deploy the current packages to all other servers.
      In this case, the destination server names are variable. Additionally, you are putting the destination server names in a comma separated format in the above package.
      Hope it helps.

Leave a Reply