On this put up I’m explaining how one can deploy a developed SSIS challenge to a number of totally different environments. It might need occurred to you that there are a number of environments that it’s essential deploy the SSIS initiatives to. Assume that you’ve DEV, QA, UAT and PROD environments. Some organisations might need much more environments. Additionally, there are lots of instances that you just might need a number of PRODs that the SSIS packages must be deployed to all of them. So the state of affairs is that everytime you create a brand new SSIS challenge in DEV space or you could modify the prevailing initiatives, it’s essential deploy every SSIS challenge to QA for testing functions. So, in case you have 3 new SSIS initiatives otherwise you’ve simply modified 3 present initiatives, you’ll have to deploy every challenge individually. It’s the identical story for QA guys after ending the check instances and after the SSIS initiatives cross all of the check instances. They’ll have to deploy all initiatives to UAT. Once more it’s the identical story with UAT and PROD. It’s getting more durable when it’s essential deploy all of the initiatives in a number of totally different PROD environments.

Utilizing the answer beneath, you’ll be able to simply deploy all SSIS initiatives from an surroundings to a different surroundings and even a number of totally different environments.

Let’s begin taking in regards to the answer.

1. Outline the next parameters:

1. Outline the next variables:

1. Put a script job on the management move space and title it “Server Names”

2. Double click on on the script job and make the modifications beneath:

a. choose “Microsoft Visible C# 2010” because the ScriptLanguage

b. On ReadOnlyVairables choose “$Bundle::DSList_str” from the checklist

c. On ReadWriteVariables choose “Consumer::DSList” from the checklist

d. Click on on “Edit Script…” button

e. Put the next scripts in the primary() methodology

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

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

checklist.AddRange(array.Break up(new char[] { ‘,’ }));

Dts.Variables[“User::DSList”].Worth = checklist;

Dts.TaskResult = (int)ScriptResults.Success;

f. Construct and save and shut the Visible Studio window

g. Click on OK on the script job editor

1. Proper click on on “Connection Managers” space and:

a. Outline a brand new OLEDB connection supervisor. Identify the connection “Supply”

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

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

       iii. Click on OK and OK once more

b. Create one other OLEDB connection supervisor in the identical method, title it “Goal”. The one factor that’s totally different is that it’s essential put “@[User::DS]” variable on the ServerName property once you’re defining the expression.

1. Put a Foreach Loop Container on the Management Circulate. Identify it “Repeat for all DSs” and make the modifications beneath:

a. Double click on on the foreach loop container, go to Assortment part and alter the Enumerator to “Foreach From Variable Enumerator”

b. From “Enumerator configuration” sections choose “Consumer::DSList” variable

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

d. Click on OK

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

2. Drop an Execute SQL Activity on the “Repeat for all DSs” container. Identify it “Acquire Catalog Knowledge”.

3. Double click on on the Acquire Catalog Knowledge and make the next modifications:

a. ResultSet: Full end result set

b. Connection: Supply

c. SQLSourceType: Direct enter

d. SQLStatement: choose p.title ProjectName, f.title FolderName from [SSISDB].[catalog].initiatives p be a part of [SSISDB].[catalog].folders f on f.folder_id=p.folder_id

e. Go to “Outcome Set” and put “0” beneath end result set and choose Consumer::OBJ from the variable checklist

f. Click on OK

1. Drop one other foreach loop container on the earlier foreach loop container and title it “Repeat for all Folders and Tasks”

Double click on on the “Repeat for all Folders and Tasks” and make the next modifications:

i. Go to assortment part and alter the enumerator to “Foreach ADO Enumerator”

ii. From Enumerator configuration choose “Consumer::OBJ”

iii. Enumeration mode: Rows within the first desk

iv. Go to Variable Mappings part and put the next settings:

Variable	Index
Consumer::Undertaking	0
Consumer::Folder	1

1. Join “Acquire Catalog Knowledge” to “Repeat for all Folders and Tasks”

2. Put an Expression Activity on the “Repeat for all Folders and Tasks” foreach loop container and title it “Create Folders Expression”

Double click on on the expression job and put the next code within the expression part and click on consider expression after which click on OK:

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

if not exists (choose 0 from [SSISDB].[catalog].[folders] the place title = N’”+ @[User::Folder] +”‘)

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

1. Drop an Execute SQL Activity on the “Repeat for all Folders and Tasks” and title it “Execute Create Folders Expression”

Double click on on the “Execute Create Folders Expression” and make the next modifications then click on OK:

i. In Common part, Connection: Goal

ii. SQLSourceType: Variable

iii. SourceVariable: Consumer::SQL

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

2. Drop an execute course of job on the “Repeat for all Folders and Tasks” and title it “Undertaking Deployment”

3. Double click on on the “Undertaking Deployment” and make the next modifications:

i. Go to course of, Executable: %windirpercentsystem32cmd.exe

ii. Go to Expressions and click on on “Expressions” from the precise pane and the clicking on the ellipsis button. The choose “Arguments” from Property and enter the next expression within the expression space:

“/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 on OK and the OK

1. It’s best to see one thing like this

1. Press F5 to execute the package deal

1. We’re performed!

The way it works

To start with we’re changing the vacation spot servers from comma delimited string to a desk formed checklist. That is performed through the use of the “Server Names” script job. So we’re changing the server names from “UATSRV01SQL, UATSRV01, UATSRV03SQL” to

This checklist is getting handed to the “Repeat for all DSs” foreach loop container, in order that the container repeats all of its subsets for every of the above checklist’s members.

When the “Repeat for all DSs” foreach loop container begins working it passes the server names to the “Goal” connection supervisor, in order that it will probably connect with the vacation spot servers one after the other.

The “Acquire Catalog Knowledge” execute SQL job connects to the supply server and collects the wanted information from the supply SSISDB. Should you run the SQL script that now we have used on this execute SQL job and run it on SSMS when related to the supply server you’ll see a end result set like beneath:

The above end result set is saved within the “Consumer::OBJ” variable. This variable is used as an enter for the second foreach loop container named “Repeat for all Folders and Tasks”. We now have mapped the “Consumer::Folder” to the second column of the above desk and “Consumer::Undertaking” to the primary column. So the “Consumer::Folder” variable accommodates the supply SSIS Catalog folders and the “Consumer::Undertaking” variable accommodates the supply SSIS Catalog challenge names beneath the SSIS Catalog folders.

Therefore, the “Execute Create Folders Expression” execute SQL job creates the folders within the vacation spot SSIS Catalog utilizing the identical folder names exists within the supply SSIS Catalog.

Up to now now we have created the identical folder because the supply SSIS Catalog within the vacation spot SSIS Catalog. Now the “Undertaking Deployment” execute course of job will deploy the initiatives beneath every folder from the supply SSIS Catalog to the vacation spot SSIS Catalog. To take action, we’re operating the ISDeploymentWizard command within the home windows console (CMD.EXE).

Analysing the expression used within the “Undertaking 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’re passing a command to cmd.exe

· ISDeploymentWizard is loading the SSIS Deployment Wizard software

· /S forces the ISDeploymentWizard to run in silent mode

· /ST:Server represents supply sort. On this case that we’re deploying the SSIS initiatives to SSIS Catalog we put Server after the colon (:) signal.

· /SS: represents Supply Server which is your supply SQL Server occasion title. As an illustration DEVPC01SQL1

· /SP: represents the supply path of the SSIS Catalog that must be one thing like /SSISDB/{Folder Identify}/{Undertaking Identify}

· /DS: represents vacation spot server that’s the vacation spot SQL server occasion title