Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Jul 13 '11 at 16:57

This question came from our site for professional and enthusiast programmers.

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
    
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 '11 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 '11 at 8:53
    
Glad to help... –  joeqwerty Jul 14 '11 at 10:37

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'
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.