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

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

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;

 

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.

       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

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

 

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

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

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

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

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

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 “

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

1. Press F5 to execute the package

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.