Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS package using wrong configuration settings Expand / Collapse
Author
Message
Posted Tuesday, August 6, 2013 6:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:12 PM
Points: 5,615, Visits: 6,413
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1481281
Posted Tuesday, August 6, 2013 6:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:18 AM
Points: 13,545, Visits: 11,359
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1481293
Posted Tuesday, August 6, 2013 6:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:12 PM
Points: 5,615, Visits: 6,413
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1481298
Posted Tuesday, August 6, 2013 7:07 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:18 AM
Points: 13,545, Visits: 11,359
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1481304
Posted Tuesday, August 6, 2013 7:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:12 PM
Points: 5,615, Visits: 6,413
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1481313
Posted Tuesday, August 6, 2013 7:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:12 PM
Points: 5,615, Visits: 6,413
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1481330
Posted Tuesday, August 6, 2013 1:31 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:18 AM
Points: 13,545, Visits: 11,359
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.




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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1481523
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse