Update existing Maintenance Plan Connection information

Background:

Anytime you inherit a system or if you start a new job you may encounter some systems or items not performing as expected. I recently found a Maintenance plan which had been running and stopped months back.

The error code indicated that the server could not be found (standard error 26)

Looking at the maintenance plan itself I found that the server name was ending 003 when the server was actually 002. Looking at history I found the server was rebuilt and renamed in February when this job stopped working. Since the Server name in the connection is grayed out, how can we update it?

Setup and Code:

(SQL 2008, also SQL 2005 if sysssispackages is changed to sysdtspackages90)

SELECT  x.*,
        LocalServerConnectionString = cm.n.value('declare namespace  DTS="www.microsoft.com/SqlServer/Dts";(DTS:ObjectData/DTS:ConnectionManager/DTS:Property[@DTS:Name="ConnectionString"])[1]', 'varchar(1000)')
FROM (
    SELECT  id, name, packageXML = CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML)
    FROM dbo.sysssispackages
    WHERE id IN (SELECT id FROM dbo.sysmaintplan_plans)
) x
CROSS APPLY packageXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";/DTS:Executable/DTS:ConnectionManager[DTS:Property[@DTS:Name="ObjectName"]="Local server connection"]') cm(n)

(SQL 2012)

SELECT x.*,
LocalServerConnectionString = cm.value('declare namespace DTS="www.microsoft.com/SqlServer/Dts";DTS:ObjectData[1]/DTS:ConnectionManager[1]/@DTS:ConnectionString', 'varchar(1000)')
FROM (
SELECT id, name, packageXML = CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML)
FROM dbo.sysssispackages
WHERE id IN (SELECT id FROM dbo.sysmaintplan_plans)
) x
CROSS APPLY packageXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";/DTS:Executable/DTS:ConnectionManagers/DTS:ConnectionManager[@DTS:ObjectName="Local server connection"]') p(cm)

If you examine the packageXML and determine it is safe to replace every instance of OldServerName with NewServerName, this would do it:

UPDATE dbo.sysssispackages SET packagedata = CAST(CAST(REPLACE(CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)), 'OldServerName', 'NewServerName') AS XML) AS VARBINARY(MAX))
WHERE id = 'package GUID'

If necessary you can make the changes manually. You could also destroy the package if you’re not careful.

DECLARE @packageXML XML;
SET @packageXML = 'Copy and paste the packageXML data, then edit it manually';
UPDATE dbo.sysssispackages SET packagedata = CAST(@packageXML AS VARBINARY(MAX))
WHERE id = 'package GUID'

Resolution:

This article shows how to update the connection properties of a SQL Server Maintenance Plan without having to rebuild the plan from scratch in the case of a server name change.

Information on Terms:

1. Maintenance Plan – SQL Servers built in mechanism to schedule and coordinate routine database maintenance items like index rebuilds or integrity check operations.

Subject Links:

Scott Coleman – https://serverfault.com/questions/289995/renamed-a-sql-server-however-maintenance-plans-still-have-old-name