locked
Environment Variable Package Configuration RRS feed

  • Question

  •  

    Okay - this one is driving me batty.

     

    I have a package that uses an environment variable package configuration of value X for a connection string.  I close BIDS.  I change the value of the environment variable to value Y.  I open BIDS and the package, and the value of my connection string is Y.  I save my package with the new configuration.  if I look at the dtsx file, I see connection string with value Y.  All as expected.

     

    I move the package to my server (I've tried Import package from SSMS, using the deployment manifest, and save copy as).  On the server, the environment variable is set to value Y.  If I run the package or export it; however, the value of my connection string is X!

     

    Does anyone have any suggestions of things to try or some reason that this is not working?

     

    Thanks,
    Jessica

    Monday, December 17, 2007 5:15 AM

Answers

  • Restarting a machine may not be a viable option every time you deploy a SSIS configuration change.

     

    You do not have to restart the machine. Environment variables are cached per-process. That is why you need to restart BIDS when developing for packages to take effect. On a server is common to use SQL Server Agent to run the package, so restart the SQL Server Agent service for the new variables to take effect.

    Monday, December 17, 2007 11:30 AM

All replies

  •  

    Try restarting the machine after  changing the value of env variable.
    Monday, December 17, 2007 6:30 AM
  • Restarting a machine may not be a viable option every time you deploy a SSIS configuration change.

     

    You do not have to restart the machine. Environment variables are cached per-process. That is why you need to restart BIDS when developing for packages to take effect. On a server is common to use SQL Server Agent to run the package, so restart the SQL Server Agent service for the new variables to take effect.

    Monday, December 17, 2007 11:30 AM
  • Although Dennis's response is certainly correct, my personal belief is that the values configured through environment variable configurations should be those values that will not change after the SSIS solution is deployed. If you know that you will need to change the configured value, use an indirect configuration (I prefer indirect XML configurations, but your needs may vary) so that the environment variable will always point to the same config path, and the config data can be changed simply by editing the file (or database values) and the packages using the configuration will immediately see the change.

     

    Monday, December 17, 2007 2:46 PM
  •  

    It seems to have magically fixed itself over the weekend (I'm assuming they restarted either the SSIS service or the server itself).  Thanks for your help!
    Monday, December 17, 2007 5:03 PM
  • Michael, I agree with the sentiment, and my thinking applies to indirect configurations even more so. The use case is you have a SSIS system deployed, and you add some new packages that have new data sources and therefore a new environment variable for the indirect configurations relating to those new connections. You are extending an existing live system with an incremental deployment so would rather avoid a full reboot and subsequent down-time, but a quick service restart you should be able to slip in without anyone noticing.

     

    (Dennis?)

     

    Monday, December 17, 2007 5:15 PM