If you are using the SSIS Integration Services Catalog and you run SSIS packages in SQL Agent jobs, you are probably taking advantage of the environments feature in SSIS (2012 +). Environments map to individual servers, servers that could be members of a mirror, dev/QA/prod, or servers that are part of a server migration. Assuming you run all of your SSIS on a dedicated server, this gives you a lot of flexibility as to where a package is executed. When you setup an Agent job you tell it what environment you want the job to run in. From time to time you'll need to change this environment during a fail-over or migration or some other event. This is where it gets tricky--there is no easy way to update a large number of SQL Agent jobs, not without modifying string fields in MSDB tables. Honestly, I'm surprised Microsoft hasn't done anything to improve this yet.

I am posting this right from the script I wrote hopefully making it easier to save off to your own script collection. There are 2 parts to this script, the first part shows you how to change the environment reference by modifying the msdb..sysjobsteps table directly, the second part shows you how to modify the jobs using sp_update_jobstep . If you are a go-by-the-book type of person you might want to refer to the second part of the script, otherwise you can light a hundred dollar bill on fire, snapchat a picture of your ass to your boss, and then refer to the first part of the script.

-- The DGAF method

/* First we need to find the environment we are currently using and
   the environment we plan to use. Each of these correspond to 2 different
   servers. */

SELECT
   Reference_ID
  ,Environment_Name
  ,Environment_Folder_Name
FROM SSISDB.internal.environment_references

/* Make a note of your environments, current and target. We are looking for the reference ID that
   corresponds to the current server so we can pass this reference_id as a filter. We are going to update the
   command line in the agent job for all jobs that refer to this environment reference. */

/* Now that you know the environment you want to target, next you can run the query below and filter
   on that environment. We are just selecting first so we can see what we'll be changing, then we will
   update. */

SELECT DISTINCT
   SUBSTRING(js.command, (PATINDEX('%/ENVREFERENCE %', js.command) + 14),2) AS AgentEnvironmentRef
  ,job_id
  ,step_id
  ,command
FROM msdb..sysjobsteps js
WHERE SUBSTRING(js.command, (PATINDEX('%/ENVREFERENCE %', js.command) + 14),2) = '8'; -- '8' is the environment reference_id, this is what we want to change.

/* If we want to update all job steps that reference the environment
   associated with reference_id 8, we could run the following. This will change
   all job steps that reference environment 8 to environment 5 */

--UPDATE msdb..sysjobsteps
--SET command = REPLACE(command, '/ENVREFERENCE 8', '/ENVREFERENCE 5' )
--WHERE SUBSTRING(command, (PATINDEX('%/ENVREFERENCE %', command) + 14),2) = '8'

/* All of this assumes you are comfortable updating the sysjobsteps table directly,
   which many of us are not and it is a practice that Mircosoft doesn't recommend.
   So here is another way we could implement this change using the msdb.dbo.sp_update_jobstep sp. */

--=================================================================

-- The "I only go by the book" method

-- Create a temp table to store job details

CREATE TABLE #Refchange (
   AgentEnvironmentRef nvarchar(5) NOT NULL
  ,job_id uniqueidentifier NOT NULL
  ,step_id int NOT NULL
  ,command nvarchar(2000) NOT NULL );
  
-- Load your temp table with the jobs/steps you want to change        

WITH CTEazyE AS (
   SELECT DISTINCT
      SUBSTRING(js.command, (PATINDEX('%/ENVREFERENCE %', js.command) + 14),2) AS AgentEnvironmentRef
     ,job_id
     ,step_id
     ,command
 FROM msdb..sysjobsteps js
 WHERE SUBSTRING(js.command, (PATINDEX('%/ENVREFERENCE %', js.command) + 14),2) = '8' )
INSERT INTO #Refchange
SELECT
   AgentEnvironmentRef
  ,job_id
  ,step_id
  ,command
FROM CTEazyE c
INNER JOIN SSISDB.internal.environment_references e ON c.AgentEnvironmentRef = e.reference_id
WHERE AgentEnvironmentRef = '8';

-- Take a look at what we have now

SELECT * FROM #Refchange;

-- Update the command field in our temp table, modify the environment reference

UPDATE #Refchange
   SET command = REPLACE(command, '/ENVREFERENCE 8', '/ENVREFERENCE 5' )
WHERE SUBSTRING(command, (PATINDEX('%/ENVREFERENCE %', command) + 14),2) = '8'

/* Now we should have our job, job step, and environment details in our temp table.

Next we need to update each job to use the new environment using sp_update_jobstep. */

DECLARE
   @jobid uniqueidentifier
  ,@stepid int
  ,@newcommand nvarchar(2000)
  
DECLARE UpdateJobCur CURSOR STATIC FORWARD_ONLY
FOR
SELECT
   job_id
  ,step_id
  ,command
FROM #Refchange

OPEN UpdateJobCur
WHILE (1=1)
BEGIN
FETCH NEXT FROM UpdateJobCur INTO @jobid, @stepid, @newcommand

IF @@FETCH_STATUS <> 0 BREAK

SELECT 
   @jobid
  ,@stepid
  ,@newcommand
EXEC msdb.dbo.sp_update_jobstep
     @job_id = @jobid
    ,@step_id = @stepid
    ,@command = @newcommand
END

CLOSE UpdateJobCur;
DEALLOCATE UpdateJobCur;
GO

Make sure you use caution when making changes that affect the SSIS environments in Agent jobs, you could really mess something up if you don't understand what you are changing. Microsoft needs to implement a better way to do this. The connect item can be seen here.

Data

Modifying the SSIS Environment Reference in SQL Agent Jobs That Run an SSIS Package in the Service Catalog

Nisor ness