Dynamically Changing Connection Properties

• Jafar

  Mr or Mrs. 500

  Points: 520

  More actions

  October 16, 2008 at 4:41 am

  #125215

  I'm trying to build a package that pulls data from a DB server that is mirrored. I already have an Execute SQL task component that queries the witness server to check which is currently the principal. The SQL task then passes the value to a variable. The problem is, I'm not sure how to go about setting the rest of my tasks to connect to the principal. Can somebody give me an idea?

  Thanks.

  Jafar

• hodgy

  SSCertifiable

  Points: 5795

  More actions

  October 16, 2008 at 6:57 am

  #885772

  1. Have the SQL task store the server name of the principle in a variable

  2. Set up a OLEDB connection to the current principle database. Rename this to Principle.

  3. In the properties for this connection, use an expression to set the ServerName with the variable from 1.

  You can then use the "Principle" connection throughout your package, and it will change dynamically when the witness server publishes the change.

  Tom

  Life: it twists and turns like a twisty turny thing