My work today on SQL Server Compact Edition (see yesterday) has gotten interrupted by some issues with a SQL Server Integration Services (SSIS) package. We keep our connection strings inside environment variables, and had this one package that just would not read those environment variables correctly. To further compound our fun, our development platform is using a 32 bit version of SSIS but our test server is running 64 bit SSIS.
We finally corrected the issue by opening the package in BIDS (Business Intelligence Developer Studio, basically Visual Studio with some SQL Server components loaded into it). Once open, we deleted the connections, recreated them, and then redeployed the package.
And ta-da! It suddenly started working correctly. I put this out here for two reasons: first, to pass along our knowledge in case you are having the same issue. Second, if you’ve had this issue I’m curious to know about it, please leave a comment and let me know your experience.
6 thoughts on “SSIS Package Not Reading Environment Variables”
I just have had the same experience as you but with configuration files. Closing and reopening the solution suddenly fixed the problem.
Why i haven’t a clue.
I was getting an error message to that effect, even though all my other packages worked correctly with those same environment veriables. I had a master package invoking two child packages, as well as doing some SQL tasks. Turned out that the second child package had a script bug that I hadn’t caught earlier. It’s failure was passed to the master package and reported out as an environment variable issue. Go figure.
Environment variables are cached when a process starts, so adding a new variable is not visible until you restart the process, be it BIDS or SQL Agent or something else.
Even i am having the same problem with the configuration file. The packages are not getting executed successfully when I am giving the configuration file path in the environment variable, Can anyone please help me out ?
I am executing the packages from the c# code.
Hello there, You’ve done a fantastic job. I will certainly digg it and personally suggest to my friends. I’m confident they’ll be benefited from this website.