SSIS Package Not Reading Environment Variables

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.

About these ads

6 Responses to “SSIS Package Not Reading Environment Variables”

  1. Peter Lundquist Says:

    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.

  2. Pat Mc Says:

    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.

  3. Darren Says:

    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.

  4. Mahesh Says:

    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 ?

  5. Conversation Topics Says:

    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.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 104 other followers

%d bloggers like this: