Post reply

Run Script on multiple SQL Server Instances

  • May 16, 2011 at 10:22 am

    #239741

    I need to create an SSIS package that will run a script on multiple SQL Server Instances.

    I need to know how to set up a connection manager that will scroll through a list of 88 instances, connecting to each and running the script on each.

    I there some way to do this without creating 88 separate Connection Managers?

  • May 16, 2011 at 10:28 am

    #1325264

    Absolutely. You need to look at the for-each loop component. As well as you will use an expression to change the login information. I hope (pray?) that you can do all trusted connectivity to the servers, otherwise you have to deal with usernames and passwords. What version of SSIS, 2005, 2008?

    CEWII

  • May 16, 2011 at 10:56 am

    #1325296

    The SSIS package will reside on a SQL2008 server but it will connect to both SQL2005 and 2008. I have trusted connections across the board so all I have to worry about is the instance name.

  • May 16, 2011 at 11:18 am

    #1325316

    Attached is a package. Pay particular attention to the expression on the remotedb connection. The package takes a query that generates a list of servers and the for-each components uses that list to iterate through and fill out the servername which the connection uses.. Let me know if you have any questions.

    CEWII

  • February 28, 2013 at 10:18 pm

    #1592235

    Thanks. This helped me.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply