December 9, 2010 at 4:56 am
Hi All,
I have to run one alter statement in 50 servers and 1 database per server. I have the list of servers and database in one table. How can i configure the connection manger to achieve this
eg: execute script1 in SQLserver1 /DB1 then connect to SQLserver2 /DB1 execute etc till SQLserver50 /DB1
Thanks,
Roshan
December 9, 2010 at 7:49 am
you could make the connection dynamic (ie., can change its value (servername & DB name) at runtime).
there is a video here which tells you on changing the value dynamically by using varaibles-http://www.sqlservercentral.com/articles/Video/64262/
In order to achieve this, define two user defined package level variables servername and databasename and
As you say you have servernames & database names in a table, fetch it and in write a script to assign it to the above defined variables at runtime
hopefully this might work for you.
December 9, 2010 at 8:03 am
I do something similar to poll all of my DB servers:
1) Set up a SQL Task and populate a recordset user variable with server instances and database names
2) Set up a For Each loop to loop through the previous recordset variable and assign values to 2 more user variables for current instance and current database
3) Use an expression in your Connection Manager utiilizing the current instance and current database variables in the connection string.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply