August 6, 2013 at 6:26 am
I've seen this problem happen since 2005, it's still happening in 2k8, and I'm trying to figure out the root cause.
SSIS packages evaluate configurations (XML at least) in the order of:
1) what they're told to look for (say in a job or manual execute step)
2) the path saved in the package
3) default values in the package itself
So it makes sense that when #1 is not available, it defaults to #2, and when #2 is also not available, it defaults to #3.
I created a package on my desktop, saved our connection managers and variables in an XML file (dtsconfig) that were all pointed to our Dev environment. Then I use the Dev dtsconfig as a template for our Test environment (just updating the values in the file itself rather than in the package).
I save the Test dtsconfig out to our APP server, open up IS in SSMS and import the package from my desktop. We use SQL Server jobs that run OS command line step types with a line that runs the package using the Test dtsconfig file.
dtexec.exe /DTS "\File System\Project\MyPackageName" /SERVER "TestServername"
/CONFIGFILE "\\TestServername\ConfigFiles\MyPackageName.dtsConfig"
/CHECKPOINTING OFF /REPORTING V
Usually this works without issue. We do it this way because we get way more logging information than we would if we just used the IS step type.
On occasion we have a package that insists on using the package values instead of actually using the designated config file. When that happens, we have to open the package, strip out the hard coded values, setting properties to not evaluate upon open, and disconnect the local config file settings. This is a good work around. But it is an annoying one.
The current problem is a package that has never had this problem before. We have never had to strip it to get it to work. Recently I made some minor changes to the package (updated a Data Flow Task), saved the package, uploaded it to Test, and now it is behaving badly by using the package defaults instead of the Test config file. I made also changes to this package last month and when we tested it, it worked fine (did not follow this bad behavior).
I'm at a loss as to why a package that recognized the config file last month suddenly does not recognize it this month. Can someone assist me with understanding this behavior?
August 6, 2013 at 6:41 am
Quote from this MSDN page: Package Configurations.
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. (The one exception to this is the Parent Package Variables configurations. The utility applies these configurations only once and later in the process.)
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. (Again, the exception to this rule is the Parent Package Variables configurations). 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.
Are you being bitten by number 4?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 6, 2013 at 6:55 am
Koen Verbeeck (8/6/2013)
Quote from this MSDN page: Package Configurations.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. (The one exception to this is the Parent Package Variables configurations. The utility applies these configurations only once and later in the process.)
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. (Again, the exception to this rule is the Parent Package Variables configurations). 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.
Are you being bitten by number 4?
It would seem so, but #4 does state that it uses command-line options to reload the config file. So why is it not reloading those when it did before without problem?
I've verified that the config file is the same, that the job step command-line is exactly the same. The only changes I made were to fix the metadata of my Source and Destination in the Data Flow Task due to an additional column being added in the schema.
And I'm not using a Parent Package. This is a stand alone package that we keep in TFS.
August 6, 2013 at 7:07 am
What SSIS is doing is the following (if I read it correctly, because it is such a mess):
1. Load configurations specified in the package configuration window during design time.
2. Check the command line. With the command line, you can for example alter a connection string pointing to a configuration specified in step 1, effectively altering the configuration.
3. Reloading the configurations of step 1. If step 2 altered something, it will load thus new configurations.
However, if you specify a brand new config file in step 2, it will be overwritten by the design time configurations in step 3.
Why don't the other package fail? I dunno. Bad SSIS mojo. 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 6, 2013 at 7:21 am
I've tested the Run Package in the GUI using the Test config file and verified that it's looking for my source file in Dev instead of Test.
So I pulled down the Test config file to my desktop to ensure it's only pulling from that. I've opened the package in BIDS, and verified that my DFT Source is pointing to the Test NAS share. When I execute the task, it fails because it doesn't see a file in the Dev environment. How terribly annoying this is getting.
I've got to catch this path switching in the act if i'm going to resolve this issue, i Think.
August 6, 2013 at 7:48 am
OH FOR HEAVEN'S SAKE!
Cannot load the XML configuration file. The XML configuration file may be malformed or not valid.
Yes, it really is THAT simple. Sigh.
Thanks for the assist, Koen, but apparently my issue is not what I thought it was.
EDIT: To clarify, my dtsconfig file had one of the recently added properties missing a "</Configuration>" end tag.
August 6, 2013 at 1:31 pm
Brandie Tarvin (8/6/2013)
OH FOR HEAVEN'S SAKE!Cannot load the XML configuration file. The XML configuration file may be malformed or not valid.
Yes, it really is THAT simple. Sigh.
Thanks for the assist, Koen, but apparently my issue is not what I thought it was.
EDIT: To clarify, my dtsconfig file had one of the recently added properties missing a "</Configuration>" end tag.
Yes, those validation warnings can be useful sometimes 😀
It's the only reason I turn on warnings in my error logging.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply