Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Configuration File drama

Status
Not open for further replies.

Catadmin

Programmer
Oct 26, 2001
3,097
US
This is a weird one.

We have 3 instances, Dev / Test / QC, on one server. Each instance has its own drive (1, 2, 3) with a config file folder. So, Dr1:\ConfigFiles\..., Dr2:\ConfigFiles\..., and Dr3:\ConfigFiles\... are the drives. Because of federal law (Sarbanes-Oxley), we have to move all our programs through each environment and cannot edit the actual package between Test & QC and QC & Production, and so we use configuration files.

We have an XML configuration file in each package with a single entry pointing to an EnvironmentConfig database on each Instance. Then we have a SQLServer configuration on the package which tells it to connect to that DB and pull all the variables in. Because we have 3 instances on one server, we CANNOT use the "Configuration location is stored in an environment variable". We save the XML config to the Test instance UNC path (from BIDS), which sets this as the package default. Everything is good so far.

We run the data update in the DB to set the variables & Connection strings. It works like a charm. We upload the package in SSMS to the Test folder under Stored Packages. We right-click the package, navigate to Run Package -> Configurations, add the configuration file and execute the package. Wunderbar! Everything is good.

Then we move the package to QC, without opening it up in BIDS. We import it via SSMS, make sure the data update is done to the QC EnvironmentConfig database, edit the XML file on the QC Dr to connect to the QC EC database & save the file. We right-click the package, add the new configuration file & QC path, click Execute. It fails. When we schedule it as a job, the error trapped says it's looking at the Test path config file instead of the QC path config file.

ARGH!

Apparently, whatever literal path is in the actual package from BIDS is the one always used. So what good is adding the config file in the Run Package -> Configurations path from SSMS?

Has anyone run into this problem? Has anyone solved it yet? Does anyone know what causes it?

Thanks,



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top