Changing database server dynamically?

Question

Changing database server dynamically?

Chris Lee-152270

SSCommitted

Points: 1809
More actions
January 2, 2009 at 3:15 pm

#125400

Hi,
I have SQL 2005 SSIS SP2 on Windows 2003. Is there a way to detect and change database server name inside SSIS package? Thanks.
Chris

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

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

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

Chris Lee (1/2/2009)
Hi,
I have SQL 2005 SSIS SP2 on Windows 2003. Is there a way to detect and change database server name inside SSIS package? Thanks.
Chris

Your question is not very clear. Based on what logic do you want to change the server name?

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Steve Jones - SSC Editor SSC Guru Points: 734667 More actions · Answer 2

Do you mean the server against which the package is being executed? Or something else?

Chris Lee-152270 SSCommitted Points: 1809 More actions · Answer 3

Hi,

Thanks for helping out. Sorry I did not make question clear.

I am trying to make package easy to deploy from development, QA and then to production. I created a row (in table) which states which database server (dev, QA or production) the package should run against. But I am not sure how to configure the connection string dynamically.

I also have another question about how to deal with table change in SSIS. In development, I can first add new columns to both source/destination tables and then modify the package to bring new columns into destination. What happen when deploy the package to QA and production? The new columns are not exits in these two environments. Would it work if I could make database server configuration dynamically? Thanks.

Chris

COZYROC One Orange Chip Points: 28499 More actions · Answer 4

Chris Lee (1/3/2009)
Hi,
Thanks for helping out. Sorry I did not make question clear.
I am trying to make package easy to deploy from development, QA and then to production. I created a row (in table) which states which database server (dev, QA or production) the package should run against. But I am not sure how to configure the connection string dynamically.
I also have another question about how to deal with table change in SSIS. In development, I can first add new columns to both source/destination tables and then modify the package to bring new columns into destination. What happen when deploy the package to QA and production? The new columns are not exits in these two environments. Would it work if I could make database server configuration dynamically? Thanks.
Chris

When you right-click on connection manager and select Properties menu, scroll-down and find Expressions property. Use this property to define expression for the server name, like assigning a package variable to it. You can load the variable value from configuration file. You will then have different configuration files for dev, QA, production. Regarding the table columns, you have to implement script to build the columns list dynamically.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/