Unanswered: Specifing a package configuration in the job without enableing package configurations
I am working on a SSIS project in a multi server environment Test\Development\Production. I choose to use package configurations as my deployment model. I just finished testing and the packages move seamlessly from test to development. For some reason the people deploying the packages to production server can not get them to pick up the configuration file. Anyway we have this go to guy that has access to production but has never used configuration files. He worked on it for awhile and determined I need to uncheck package configurations but specify a config file in the Job configurations tab. My feeling is that this wont work but I cant find any documentation that states a job with the "Enable package configuration unchecked" will actually use a configuration file if provided, or this package will not use a configuration file even if provided.
I am sure you have checked, but the configuration file is in the same path on all three environments? After that, it could be a question of permissions on the folder/file.
All the SQL Agent job options are doing are setting up a command line call to the DTEXEC utility, so this would be a valid override of the location for the config file. However, I (speaking for myself, of course) hate using the GUI for scheduling packages, as the GUI looks for the package locally (and errors out), and I have no intention of using the GUI on the server.
Thank you, You saying If the enable package configuration is not checked in VS under SSIS\Package Configurations. DTEXEC will still use a configuration file if specified. Therefore that setting only pertains to debugging in VS. Thank you that clears up that question. I was just worried that would exclude some function from the built package that allows loading configurations. Not sure why you don't like the GUI for scheduling. It does have limits but we have used it at our company for the last few years and have had on problems.
I think you will still need to have the configuration enabled in Visual Studio (otherwise, the package would not know to populate those variables/properties from the config file to begin with), but the Configuration file defined in the SQL Agent job would override the location defined in Visual Studio. This is of course based on purest conjecture, so some testing will have to be done. It could entirely be that the config files are somehow cumulative, but I don't see how that would work out.
Ok that's where it becomes less clear. I know configurations work but the difficulty appears in what order the configuration's are applied. My production team is saying it is trying to connect to my test server first and abending the program before it applies the configuration file. From the explanation from Microsoft it sounds like they may be correct.
As the utility loads and runs the package, events occur in the following order:
1.The dtexec utility loads the package.
2.The utility applies the configurations that were specified in the package at design time and in the order that is specified in the package.
3.The utility then applies any options that you specified on the command line.
4.The utility then reloads the configurations that were specified in the package at design time and in the order specified in the package. The utility uses any command-line options that were specified to reload the configurations. Therefore, different values might be reloaded from a different location.
5.The utility applies the Parent Package Variable configurations.
6.The utility runs the package.
We managed to find the answer after a little experimentation and head banging. Its one of those once you know the answer it becomes clear. As stated on Microsoft's web site first the configurations inside the package are applied. This will be whatever it was last run with. So if it was run in development that's what it will have. Then the configuration specified on the job step is applied. In a third step the server agent reapplies the values in the package, overriding whatever is specified on the command line.
The solution is, when the package is ready to be moved, uncheck the enable package configurations and build the solution. This will remove the values stored in the package and the server agent will not overwrite the job step configuration. Microsoft one question, Why would Server Agent overwrite the run time options? logically it would seem like runtime options should trump design time options. I interrupted enable package configuration to mean I wanted to use configuration, not override my runtime options.