3

I have created a SSIS Package. I want to use Variables for development purpose in my local. But, When I move this Package to QA, I want to make use of the XML Package Configuration file(.dtsConfig) so that it will use values specific to environment. I am using Indirect Configuration File by keeping the .dtsConfig File path in environment variable & also I turned on "Enable Package Configuration".

I generated the .dtsConfig file made necessary changes to it (for environment specific values). Copied the .dtsx & .dtsConfig file to different server. Created environment variable on the server with value having path of the .dtsConfig file. Called the SSIS package from a SQL job.

But,on server it's using the Variables value not values from the .dtsConfig file. While on local, it's using values from .dtsConfig file. I want the exact opposite behavior.

What am I missing here? Is there some other property for this behavior?

Note: Adding the .dtsconfig file path in "Configurations" tab of SQL job, uses values from the package config file. But I want to make use of Environment Variables for .dtsConfig file path

Rasmita Dash
  • 917
  • 5
  • 15
  • 28
  • What is in the execution log, if you specify detailed logging? Does SSIS give a try loading dtsConfig file? – Ferdipux Dec 27 '17 at 09:02
  • I'm not so sure you can put the config file path in an environment variable. Have you seen some documentation that says you can do that? – Tab Alleman Dec 27 '17 at 14:47
  • Yes. It can be done and the concept is Indirect Configuration. https://sqlscape.wordpress.com/tag/direct-vs-indirect-configuration/ – Rasmita Dash Dec 27 '17 at 14:54

1 Answers1

1

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. [got from source on internet]

After restarting SQL Server, it started using indirect package configuration (.dtsConfig)file. But, it's not a feasible solution to restart DB server of all environments. So I preferred using SQL Job configuration. i.e. Adding the .dtsConfig file path in Job Configuration.

During development, I had to restart Visual Studio after creating environment variable. To confirm this behavior I followed following steps:

  1. Created two .dtsConfig files. Config_Error.dtsConfig & Config.dtsConfig. Names are self-explanatory. Config_Error.dtsConfig file has error. So when environment variable has Config_Error.dtsConfig file path, package should fail.
  2. Created environment variable pointing to Config.dtsConfig file path.
  3. Opened solution in Visual Studio.
  4. Executed package in debug mode. Execution succeeded as expected.
  5. Changed environment variable value to point Config_Error.dtsConfig file path.
  6. Executed package in debug mode. Execution still succeeded. But, it should have failed as .dtsConfig now has invalid values. That means, modified value of environment variable is still not available to Visual studio debugger.
  7. Restarted Visual Studio.
  8. Executed package in debug mode. Execution failed as expected. i.e. after restarting, Visual Studio got modified value of environment variable.

So, if we are using environment variable, Consuming program should be restarted after any change.

Rasmita Dash
  • 917
  • 5
  • 15
  • 28