Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS package using wrong configuration settings


SSIS package using wrong configuration settings

Author
Message
Brandie Tarvin
Brandie Tarvin
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7766 Visits: 8728
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?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16441 Visits: 13202
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?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Brandie Tarvin
Brandie Tarvin
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7766 Visits: 8728
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.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16441 Visits: 13202
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. :-)



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Brandie Tarvin
Brandie Tarvin
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7766 Visits: 8728
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.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Brandie Tarvin
Brandie Tarvin
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7766 Visits: 8728
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.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16441 Visits: 13202
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 :-D
It's the only reason I turn on warnings in my error logging.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search