Set local connection dynamically?

Question

I use maintenance plans to backup my databases and perform a few other regular maintenance tasks. As you know, maintenance plans are nothing more than SSIS packages. 

I'd like to use the same maintenance plans on many database servers (I administer about 50) without modification. One thing I've never figured out though is how to set the local connection property for a maintenance plan / ssis package at run time. Is this possible? Id like to do something like "select @@servername" to set the local connection property for the MP. I'd also like to put the same value into the subject of the notification tasks if a task fails.

What I've done in the past when I deploy MPs on a new server is use a text editor to edit the xml files generated when I built the project in BIDS and change the server name manually, then deploy the modified packages via the deployment utility. I think there must be a better way where the local connection property gets set automatically.

Many of my servers are clustered and/or use named instances so I can't just use "(local)".

TIA

---

Chuck

Answer 1

If i understand your question correctly this URL would help Dynamic Database Connections

---

Regards, Dinesh

Answer 2

You would need to create these packages yourself. Extract the packages to your local drive and open to edit in Visual Studio or BIDS.

Then save each as another package and parametrize the connection string by adding a configuration: 

http://www.mssqltips.com/sqlservertip/1405/sql-server-integration-services-ssis-package-configuration/

Well, want to add, basically, you are better off employing a special product: Central Management Server

Start here: http://msdn.microsoft.com/en-us/library/bb895144.aspx

---

Arthur My Blog

Answer 3

Is there a way to manage maintenance plans via CMS? I'm somewhat familiar with CMS. I have one configured but all I've ever used it for was to run the same query on multiple servers.

---

Chuck

Answer 4

If I use a central management server to run the packages for my 50 servers, doesn't that become a single point of failure? IOW if the CMS is down, all maintenance plans enterprise-wide stop working?

---

Chuck

Answer 5

With CMS you may better off not using the packages, but once/since the Agent jobs are [already] set you can run them using CMS, and you can see those failing + other benefits, I just told you this is an option. Especially for a growing environment instances - wise. 

As if it a single point of failure, then I do not not think it is more or less reliable then your any machine, plus worse case you can still always go to a needed target directly, CMS just makes or will make your life easier, I can only admit it is more for a DBA (and you sounds like) than a BI developer.

---

Arthur My Blog