October 13, 2006 at 8:43 am
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,
October 16, 2006 at 8:00 am
This was removed by the editor as SPAM
November 2, 2006 at 12:49 pm
Brandie,
Take a look at my post regarding a similar problem. Title of the post is Changing connections at run time.
Lee
November 2, 2006 at 1:04 pm
Lee,
Thanks for the response. I posted a reply on your thread as to what we finally figured out the problem was. We don't have a solid resolution yet that applies to SOX, but we're making due.
November 3, 2006 at 2:19 pm
Hi Brandie,
I'm curious. Did you use the .SSISDeploymentManifest utility to deploy the pkg to your development server?
If not, then using that utility may solve your problem.
Just as background, I used the manifest to deploy a VERY SIMPLE package with its configuration file to my development server.
I checked in the dtsx file and configuration file to my SC environment.
Then using the SC tool, I copied those 2 files to my Test server.
After changing the information in the configuration file (i.e., server name, database name, and since I used a CSV file source, the location of that CSV file), I was able to run the package directly from IS in Management Studio and as a job.
When I ran it as a job, I had to make sure that it was using the correct configuration file by clicking on the Configurations tab, and selecting the path of the configuration file that I wanted to use. However, that was the only tweak that I had to perform.
So the only hard coding that I had to do was in changing the values in the XML/Config file. And you could probably write a stored proc or .NET program to populate those values dynamically using the info from your DB table.
November 6, 2006 at 4:20 am
We've tried everything we can think of to deploy properly. Our problem is that we don't have separate servers for Dev/Test/QC. We've got the same server for all three with separate instances and separate logical drives for each instance.
On top of this is a federal law called Sarbanes-Oxley which we fall under. According to SOX, once you move a program from Development, you are not supposed to edit it (or anything related to it like a configuration file) when you move it through the Software Development Life Cycle (SDLC). If you do, you have to take it all the way back to Dev and start the SDLC all over again. SOX isn't too understanding when it comes software that just isn't combatible with such a concept. So, until we get our middle-tier app server and can run SSIS from there, we're just going to have to keep editing files and hope we don't get a federal audit.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply