Wednesday, May 19, 2010

Same Connection Manager on Multiple Server

There was a question on MSDN forum to Run same query on Mulitple Servers.

We can do this by using one Connection Manager and passing the Connection String through Expressions
Below are the steps I followed to achieve this

1. Store Connection String for various connection in a table

2. Declare two Varibles like

3.Use Expression of Source Connection Manager to provide Connection String

4. Use Execute Sql Task to get connections in a Varible of type Objects from the table

5. Use For Each Loop to Iterate on that variable

6. Use Script component to cast the value of CurrConnection to String
        public void Main()
            // TODO: Add your code here
            Dts.TaskResult = (int)ScriptResults.Success;
            String con;
            con = (string) Dts.Variables["CurrConnection"].Value;
            Dts.Variables["CurrConnection"].Value = con;

7. Use any task inside the for loop with Connection Manager and it will use different server for each loop.


Anonymous said...

Thanks but not enough information... Can you elaborate on the step by step process. For example why you are using a script task and not an execute task via expressions in the connection manager thanks

Post a Comment