SSIS package not picking up configuration files correctly

Question

We have recently migrated some 30 DTS packages in SQL Server 2000 to SSIS packages in SQL Server 2008. We have created packages in such a way that all environment related variables and other required information are picked from configuration files for maintenance of packages across different environments like Dev, QA and Prod.

After setting up all the packages with the config files, when we tested the packages from Business Intelligence Development Studio, it worked fine and picked up the values from the configuration file. And when changing the values in config files to Dev or other env it correctly picked up the values and executed. Similarly, tried for 2 different environments and the packages worked fine. So we deployed to Prod and it was working fine.

Yesterday, I had to make a functional change for one package and so I made the change in the package (it is just changing a parameter in a SQL procedure execution task and not related to any variables) and tested in BIDS with 2 environments and it worked fine.

As the change was not related to any environment change, we deployed only the updated package (not the associated config file) manually in Prod (i.e without the use of manifest). The config file which was used by the package previously and working fine in Prod remained unaltered. But when the package was executed, the package was pointing to QA and the package didn't read from the config file I believe.

One reason may be, it is still using the last executed values which remains in the .dtsx file(can be checked by opening the file in a text editor) usually. But normally, when a package is executed, the values will be overwritten from config file. Guess it is not happening.

What are the possible reasons for this? We have tested extensively switching between test environments and it does not show this behavior. We have encountered this in Prod environment twice now. Anyone else have experienced this and how have you resolved this?

Answer 1

Check the environment variable on prod, maybe someone changed it for testing something and forgot to change it back?

Answer 2

How are you running the packages?

What I found works, is to create a SQL Agent job and assign needed config file there, in step properties. You would end up with one job per environment with different paths to the config files.

Answer 3

We finally and hopefully found a workaround for this issue. After running the packages in test environment and tested it, we point it to Prod environment and add a dummy condition to make it fail in the first task and then execute the package. By doing this the variable values stored in the .dtsx file are refreshed with Prod values from the configuration files. Then we create the manifest and deploy it. But remember to remove the dummy condition which forcefully fails the first task. It's working as of now. I will update if i find an actual solution to this problem.

Answer 4

Just getting my feet wet, but it looks like there is an issue with picking up config files after the package is imported to a server. Did you change the config before importing the package?