May 24, 2011 at 2:59 am
Excuse in advance my lack of knowledge in this area!
I have a package which uses a couple of package config files (defined in the package under SSIS/Package Configurations). This job was moved to production and scheduled in SQL Agent (with the config files specified under the Configurations tab for the relevant job step).
My expectation was that the package config files would be overridden by those defined in the job. But the package failed with validation errors suggesting that it was still looking for the package config files (local file path not valid on production server), regardless of the job settings.
So ... is my understanding correct?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 24, 2011 at 3:23 am
I know this is the SQL Server 2005 forum, but people usually post SSIS questions all over the place, so I'll ask to be sure:
is it SQL Server 2005 or 2008 you are working with?
The reason I ask this because there is a behavioural difference between the two versions.
Read more here:
2008: http://msdn.microsoft.com/en-us/library/ms141682(v=SQL.100).aspx
2005: http://msdn.microsoft.com/en-us/library/ms141682(v=SQL.90).aspx
The culprit is that configurations are applied multiple times. The problem is that it isn't explained what the Agent configuration actually does. Maybe you can check the dtexec command that the jobstep generates and post it here?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 24, 2011 at 4:11 am
Thanks for the quick response - this is SSIS 2005.
Here is my command line, edited slightly to protect the innocent:
/FILE "T:\SSIS\Packages\abcde\bin\abcde - ERD.dtsx" /CONFIGFILE "T:\SSIS\ConfigFiles\Common\SMTP.dtsConfig" /CONFIGFILE "T:\SSIS\Packages\abcde\Config\Custom - ERD.dtsConfig" /CHECKPOINTING OFF /REPORTING E
The two defined config files are valid & get picked up OK.
The error is along the lines of
The configuration file name "C:\Dev\xxx - ERD.dtsConfig" is not valid. Check the configuration file name. End Warning Warning: 2011-05-24 07:10:01.89 Code: 0x80012012 Source: xxx - ERD Description: The configuration file name "C:\Dev\SSIS\_CONFIGFILES\_CON_SMT_SendMail.dtsConfig" is not valid. Check the configuration file name. End Warning Warning: 2011-05-24 07:10:01.89 Code: 0x80012059 Source: xxx - ERD Description: Failed to load at least one of the configuration entries for the package. Check configurations entries and previous warnings to see descriptions of which configuration failed. End Warning Warning: 2011-05-24 07:10:05.10 Code: 0x00000001 ... The package execution fa... The step failed.
So ... there is clearly no mention of the erroring config files in the command line and I therefore assume that the config entries from the package-defined config files are loaded first and then overwritten, if repeated, by the job-step config files, though that is untested.
If this is true, it seems to mean that the paths/filenames defined for config files during development need to be available on the production server to avoid such errors. (Or the package is edited as needed before migration to production, which is unappealing.)
I could not see an answer to this in the links which you provided.
Thanks for any input.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 24, 2011 at 5:22 am
In the 2005 link Koen posted it says:
If the /ConfigFile option specifies a location for the configuration file that is different than the location that the package specifies, the dtexec utility might raise a warning. You can disregard this warning. As long as the /ConfigFile option specifies a valid file path, the dtexec utilty will find and use the configuration file that the /ConfigFile option specifies.
"might raise a warning" ... but you are seeing the entire job step fail correct?
On the "Execution options" tab in the SQL Agent job what do you have checked?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 24, 2011 at 5:22 am
According to the 2005 dtexec page, the /configfile switch should take precedence over the design-time configurations.
http://msdn.microsoft.com/en-us/library/ms162810(v=SQL.90).aspx
It seems odd to me that the package would actually fail when a configuration can not be loaded.
There is a property SuppressConfigurationWarnings at the package level. Can you set this to true and describe what happens when you run the package?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 24, 2011 at 5:50 am
I'm tied up with something else for a while, so cannot try your suggestion Koen, but just responding to a couple of the points:
1) Even though those config file messages just look like warnings, the SQL Agent step fails, failing the entire job.
--Maybe it failed for some other reason and did not produce an error message and these are just warnings after all.
2) None of the Execution Options checkboxes is ticked.
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 24, 2011 at 10:40 am
My suspicion at (1) above was right. Something else is failing the package and it's not being reported - the config warnings were only warnings as suggested - I'll investigate more tomorrow. Thanks for the input guys.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply