6

A colleague has renamed a SQL server, however all of the maintenance plans still have references to 'OLD_NAME' as 'Local Server Connection'.

The text boxes containing the server names for Local Server Connections are greyed-out and cannot be changed

Is there a way to change these values, short of exporting all of the SSIS packages, changing in Visual Studio and re-adding them again?

3 Answers 3

3

How did the colleague rename the server? Did they follow this procedure:

http://msdn.microsoft.com/en-us/library/ms143799.aspx

If not, they need to, The procedure should be the same regardless of SQL version.

3
  • Do you expect doing this will correct the naming issue, even if it wasn't necessarily followed the first time? (I don't know whether the guide was followed or not)
    – Richard
    Jul 14, 2011 at 7:03
  • 1
    Changed the version to SQL Server 2005 (just below title of page) and at the bottom of that page there is some prepared SQL for sorting out the local server connection server names. Excellent - thank-you
    – Richard
    Jul 14, 2011 at 8:53
  • Glad to help...
    – joeqwerty
    Jul 14, 2011 at 10:37
8

The Microsoft link given above does not address the "Local server connection" server names in maintenance plans. These queries let you check the maintenance plan connection strings. All use the msdb database.

(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'
0

Another option is:

  1. to export SSIS package of the maintenance plan
  2. open the exported package file in notepad
  3. replace "old server name" with "new server name" and save the change
  4. rename the existing maintenance plan i.e. maintenance plan old
  5. import the SSIS package saved from #3

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .