Here's my problem:
I've entered a set of database maintenance plans in SQL 2005 and would like to export them to other servers. I've built four database servers on this Windows 2003 server, all named instances. One is SQL 2000, the others are all SQL 2005. SSIS is running on this Windows server using the server name, since it doesn't support multiple instances. The maintenance plans are all functioning properly.
I want to export the maintenance plans to other servers and the research I've done directs me to go into Integration Services, open up the MSDB database and select the packages there, and select export. The problem is that when I attempt to open up MSDB I always get a connection failure. I don't know which of the SQL Servers the maintenance plans are loaded into (well, I have a hunch, but it's very possible I'm wrong) and I can't figure out how to get the SSIS server opened up properly in Management Studio. I've tried changing the MsDtsSrvr.ini.xml file and setting the <ServerName>.</ServerName> value from "."to the name of the server I think contains the data, but that caused Integration Services to not come up after the restart.
The maintenance plans I developed were quite complex due to error handling steps I included, and I'd really rather not have to build them manually for each of my servers here.
Any assistance will be greatly appreciated.
Well, I solved the problem myself, by just slowing down a bit. I stopped Integration Services on the Windows Server, changed the MsDtsSrvr.ini.xml file <ServerName> setting to the name of the server I thought contained the maintenance plans, then restarted Integration Services. After that I was able to successfully connect to the SSIS and export the maintenance plans by opening up the MSDB database, drilling down to the maintenance plan I wanted to export, right-clicking, then selecting Export. I then entered the server name of the destination server (database server, not SSIS) and drilled down to the Maintenance Plans folder, and clicking OK.
The maint plans are now where I wanted them, without having to rebuild them for each server.
No comments:
Post a Comment