[sourcecode language='sql'  padlinenumbers='true']
SET NOCOUNT ON;

DECLARE @change_type VARCHAR(10) = 'CONNECTION';            -- Type of change : CONNECTION / OWNER / ALL 
DECLARE @maintenance_plan SYSNAME = 'MaintenancePlan';      -- Name of the maintenance plan to apply changes 
DECLARE @oldservername SYSNAME = 'OLDERSERVERNAME';                -- Name of the old server that hosted the maintenance plan 
DECLARE @newservername SYSNAME = @@SERVERNAME;              -- Name of the new server that hosts the maintenance plan 
DECLARE @newowner SYSNAME = 'LANGROUP\dbn';                           -- Name of the new owner for the maintenance plan (ssis)

DECLARE @oldowner SYSNAME; 
DECLARE @debug BIT = 0; -- ACTIVE DEBUG (=1) 
DECLARE @xml NVARCHAR(MAX);

-- Verification of existing maintenance plan 
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysssispackages WHERE name = @maintenance_plan) 
BEGIN 
RAISERROR('The maintenance plan %s doesnt exist', 16, 1, @maintenance_plan); 
RETURN; 
END

-- Verification of the type of change 
IF @change_type NOT IN ('CONNECTION', 'OWNER', 'ALL') 
BEGIN 
RAISERROR('The @change_type parameter must equal to CONNECTION, OWNER OR ALL', 16, 1); 
RETURN; 
END

-- Changing of the connection string value 
IF @change_type IN ('ALL', 'CONNECTION') 
BEGIN 
PRINT 'REPLACE OLD SERVER NAME ' + @oldservername + ' BY THE NEW SERVER NAME : ' + @newservername + ' INTO THE  MAINTENANCE PLAN';

BEGIN TRY 
  SELECT  
   @xml = cast(cast(cast(packagedata as varbinary(max)) as xml) as nvarchar(max)) 
  FROM msdb.dbo.sysssispackages 
  WHERE name = @maintenance_plan;

  -- DEBUG 
  IF @DEBUG = 1 SELECT CAST(@xml AS XML) AS before_change; 
  
  -- Case when connection string use server parameter 
  IF (@xml LIKE '%server=''' + @oldservername + '%') 
  BEGIN 
   PRINT '--> Maintenance Plan with server parameter'; 
   SET @xml= replace(@xml,'server=''' + @oldservername + '','server=''' + @newservername + ''); 
  END 
    
  -- Case when connection string use Data source parameter 
  IF (@xml LIKE '%Data Source=''' + @oldservername + '%') 
  BEGIN 
   PRINT '--> Maintenance Plan with Data Source parameter'; 
   SET @xml= replace(@xml,'Data Source=''' + @oldservername + '','Data Source=''' + @newservername + ''); 
  END 
  
  PRINT 'Update maintenance plan done …'; 
  
  -- DEBUG 
  IF @DEBUG = 1 SELECT CAST(@xml AS XML) AS after_change;

  -- Validation of the xml document after replacing the connection string 
  SELECT CAST(@xml AS XML);

  -- Update ssis package with new connection string 
  UPDATE msdb.dbo.sysssispackages 
   SET packagedata = CAST(@xml AS VARBINARY(MAX)) 
  WHERE name = @maintenance_plan; 
  
END TRY 
BEGIN CATCH 
  PRINT CHAR(13) + 'An error occured during the update of the maintenance plan : ' + CHAR(13) + 
        'Error : ' + CAST(ERROR_NUMBER() AS VARCHAR(4)) + ' Severity : ' +  CAST(ERROR_SEVERITY() AS VARCHAR(4)) + ' State : ' +  CAST(ERROR_STATE() AS VARCHAR(4)) + CHAR(13) + ERROR_MESSAGE() 
END CATCH; 
END;

-- Changing of the owner of the maintenance plan 
IF @change_type IN ('ALL', 'OWNER') 
BEGIN 
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = @newowner) 
BEGIN 
  RAISERROR('The new owner %s of the maintenance plan doesnt exist on the SQL Server instance.', 16, 1, @newowner); 
  RETURN; 
END

SELECT @oldowner = sp.name FROM sys.server_principals AS sp 
INNER JOIN msdb.dbo.sysssispackages AS ssis 
  ON sp.sid = ssis.ownersid 
WHERE ssis.name = @maintenance_plan; 
  
PRINT 'REPLACE OLD OWNER ' + COALESCE(@oldowner, 'UNKNOW ') + ' BY THE NEW OWNER : ' + @newowner; 
  
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = @newowner) 
BEGIN 
  RAISERROR('The new owner %s for the maintenance plan %s doesnt exist on the SQL Server instance.', 16, 1, @maintenance_plan, @newowner); 
  RETURN; 
END 
  
-- Update owner of the SSIS package 
UPDATE msdb.dbo.sysssispackages 
  SET ownersid = (SELECT [sid] FROM sys.server_principals WHERE name = @newowner) 
WHERE name = @maintenance_plan; 
  
PRINT 'Update owner of the maintenance plan done …'; 
  
-- Update owner of the corresponding SQL job 
WITH cte_owner_job 
AS 
( 
  SELECT 
   ssis.name, 
   j.owner_sid 
FROM msdb.dbo.sysssispackages AS ssis 
INNER JOIN msdb.dbo.sysmaintplan_subplans AS sp 
  ON ssis.id = sp.plan_id 
INNER JOIN msdb.dbo.sysjobs AS j 
  ON j.job_id = sp.job_id 
where ssis.name = @maintenance_plan 
) 
UPDATE cte_owner_job 
SET owner_sid = (select [sid] from sys.server_principals WHERE name = @newowner); 
  
PRINT 'Update owner of the corresponding SQL Server jobs …'; 
  
END; 
[/sourcecode]