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"
/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?
Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog
, and Twitter
.Freelance Writer: ShadowrunLatchkeys: Nevermore
, Latchkeys: The Bootleg War
, and Latchkeys: Roscoes in the Night
are now available on Nook and Kindle.