1

When I set up my Production SQL job to execute my SSIS package which use an environment variable for configuration, the job does not use the Production config value of the environment variable on the production server. The job use the design time values instead. I created the environment variable on the same server that I exec the job (Production). Any thought?

An Vu
  • 61
  • 1
  • 5

3 Answers3

4

I figured out that I have to restart the SQL Server Agent in order for the SQL Agent to recognize the newly created Environement Variable.

An Vu
  • 61
  • 1
  • 5
1

First, ensure the Environment Variables are properly referenced, and activated. Then, when you have deployed your package, edit the Configuration of the SSIS step in question, blank out the values that are going to be set by the Env Vars. You might get an error when you click OK claiming required variables are not set. Ignore it, click Cancel to get out of the Config screen. Enter the config screen again and you will see the Env Vars are finally used. Quirky, but better than restarting your SQL Server (tested with SQL Server 2016)

wp78de
  • 18,207
  • 7
  • 43
  • 71
0

When you configure the SSIS project's parameters, make sure that the parameter is set to "Use environment variable":

  • Open the SSIS project configure dialog
  • Navigate to the parameter.
  • Press the "..." button right of it
  • Set the radiobutton at the bottom of the "Set Parameter Value" dialog
  • Choose an environment variable from the dropdown box

Afterwards you may need to create the step in your SQL Server Agent Job anew so it uses all the environment variables.

Gerhard Schmeusser
  • 31
  • 5