CODEWITHSUNDEEP
ssisssis-2012
Rasmita Dash
Rasmita Dash

Reputation: 937

Make SSIS package use Package Configuration(.dtsConfig file) instead of Variables

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

Upvotes: 3

Views: 7377

Answers (1)

Rasmita Dash
Rasmita Dash

Reputation: 937

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.

Upvotes: 1

Related Questions