Here is the scenario
you have to run the same SQL statement(s) in multiple database server/instances and you want to do it via SSIS.
Here is an approach
You can solve this issue in 3 steps:
1. Within a package, you can load all the connection strings of the target DBs in a SSIS variable of object type
2. Then you can use a ForEachLoop container to shred the content of the object variable and iterate through each of the connection strings.
3. The rest is just plain easy, place the Execute SQL task(s) with the SQL statements you have to run in all the target instances inside of the ForEachLoop container.
Where is the trick?
Use the power of SSIS expressions! Add an expression to the Connection manager used by the inner Execute SQL Task(s) to alter the connection string on each iteration with the content of a variable populated by the ForEachLoop container. For this example I use a SQL table to store the connection strings, but you could stored them in a flat file or any other place where SSIS can get a connection to. In other instances you may find more practical to write a query that list all the instances, but my preference is to store the values so I have more control over the process.
Here is the structure of the table I used:
This is how I configure the ForEachLoop container:
This is the configuration of the outer Execute SQL task
This is the configuration of the inner Execute SQL task
you can download the sample package from my Skydrive (you will need SSIS 2008):
I hope you find this helpful. Ah! and if you have questions about this post or any other SSIS issue I recomend you to post your questions in the SSIS forum: Here