After migrating SQL Server to new hardware environment or after renaming SQL Server instance, when you open Maintenance plan and see Local server connection. You notice that it still reflects old instance name.
When you click edit you don't get option to change server name. When you
execute maintenance plan you notice error "Failed to acquire connection
"Local server connection". Connection may not be configured correctly
or you may not have the right permissions on this connection" "Code:
0xC00291EC"
This is because Local server connection holds the instance name where the Maintenance plan was originally created. By default SQL 2005 maintenance plans are stored in sysdtspackages90 table in msdb. The column holding the data of maintenance plans is of data type image. In
order to decode the column cast it to XML data type using query
SELECT name,PlanXML=CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML) FROM sysdtspackages90
You can notice that old instance name is stored for Local server connection.
Resolution is to Import this package to SSMS, modify the Local server connection value, and Export it back to SQL Server. Below is the step by step guide to do that.
1. Connect to any SQL Server machine running SQL Server Integration Services through remote desktop connection.
2. Open C:\Program Files\Microsoft SQL Server\90\DTS\Binn folder location from Windows Explorer.
3. Locate file MsDtsSrvr.ini.xml and open it using any text editor like notepad.
4. Locate XML tag <ServerName></ServerName> and insert the target SQL Server instance name in which you need to modify the maintenance plan in between these tags. Ex: <ServerName>SQLCLUS\SQL2005</ServerName>
5. Save this XML file.
6. Restart SQL Server Integration Services.
7. Open SSMS (SQL Server Management Studio).
8. In the Object Explorer, Select Integration Services
9. Type just NT host name of server where SQL Server Integration Services is running and we did modification of MsDtsSrvr.ini.xml. In case of cluster, you can even type the node hostname
where SSIS service is active currently.
10. Once connected, Navigate to Stored Packages --> MSDB --> Maintenance Plans --> Right click your maintenance plan
--> Export Package.
11. New window "Export Package" should open prompting for Package Location. Choose "File System". Type target SQL Instance name where Maintenance plan is stored in the Server textbox. Select Authentication of your choice.
12. Click Package path Browse button --> SSIS Packages --> Maintenance plans --> Package Name. Choose protection level of your choice. Click OK.
13. Now MP will be saved as .dtsx file in the destination path provided above.
14. Open the .dtsx file in notepad, locate the SQL Server instance name inside the file, modify to the new instance name and save it.
15. Now delete the existing Maintenance plan in the target SQL Instance. Make sure corresponding Job is also deleted.
16. Navigate to SSIS connection we made using SSMS.
17. Right click Maintenance plan --> Import Package.
18. New window "Import Package" should open prompting for Package Location. Choose "File System". Type target disk location where Maintenance plan is stored as .dtsx file. Click on Package name and ensure that we get Package name by default. Click OK.
19. Now disconnect SSIS connection in SSMS and connect to Database Services of target SQL Server.
20. Navigate to Management --> Maintenance Plans --> Select your MP --> Right click --> Modify.
21. Click on Sub plan schedule --> Ensure that schedule is perfect --> Click OK.
22. Save the Maintenance Plan and make sure that jobs are created according to the number of Sub plans.
23. Execute individual jobs or MP to ensure that things are proper.
Please do comment if you have another shorter road which takes us to solution quicker J
