Have Geek Brain

SSIS package does not load configuration files correctly

We recently migrated about 30 DTS packages in SQL Server 2000 to SSIS packages in SQL Server 2008. We created packages so that all environment-related variables and other necessary information are fetched from configuration files to serve packages across different environments, such as Dev, QA and Prod.

After setting up all the packages with config files, when we tested the packages from Business Intelligence Development Studio, it worked fine and took the values ​​from the config file. And when changing values ​​in config files in Dev or other envs, it took the values ​​correctly and executed. Likewise, tried in two different environments and the packages worked fine. So we turned to Prod and worked fine.

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

Since the change was not related to an environment change, in Prod (without using a manifest) we only manually installed the updated package (not its associated config file). The configuration file that was previously used by the package and worked in Prod remained unchanged. But when the package was executed, the package was pointing to QA and the package was not read from the config file I believe.

One reason might be that it is still using the last executed values, which remain in the .dtsx file (you can usually check by opening the file in a text editor). But usually, when the package is executed, the values ​​will be overwritten from the config file. I don't think this is happening.

What are the possible reasons for this? We tested wide switching between test environments and it doesn't show this behavior. We encountered this in Prod twice. Has anyone else experienced this and how did you resolve it?

+2


a source to share


3 answers


Finally, we hope to find a workaround for this problem. After running the packages in a test environment and testing, we will point it to the Prod environment and add a dummy condition to trigger it in the first task and then execute the package. By doing this, the variable values ​​stored in the .dtsx file are updated with the Prod values ​​from the configuration files. Then we create a manifest and deploy it. But don't forget to remove the dummy condition that forcibly fails the first task. He is working at the moment. I'll update if I find an actual solution to this problem.



+1


a source


Check your environment variable on prod, maybe someone changed it to test something and forgot to change it back?



0


a source


How do you run packages?

What I found was to create a SQL Agent job and assign the required config file there in the properties of the step. As a result, you will receive one task per environment with different paths to the configuration files.

0


a source






More articles:


All Articles