May 11, 2010 at 8:59 am
Hi,
I have a variable, that is set within a config file, that contains the oledb connection string for my database.
Once I build and then deploy (using the .SSISDeploymentManifest) I then schedule the package to run as needed. All works like a charm.
But...when trying to force the package to fail (I have my reasons) by removing the config file, it still runs!
I was under the impression that if I do not specify a configuration file within the scheduled SQL Agent job, that the package uses the .dtsConfig I specified when I created the package initially.
But, if I remove this config file along with another other instances of it (within the file system) the job still runs! I'm baffled!
So that leads me to assume that when you deploy the package it 'installs'/places a copy of the config file somewhere else completely, perhaps in SQL Server itself...but where, maybe msdb?
Please can someone clear this up for me? (Assuming I deploy to file system and not sql server).
Thanks!
Jinx.
May 11, 2010 at 9:49 am
Does the variable have a valid 'default' value (as viewed under SSIS/Variables/Value)? Maybe this is being used.
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 11, 2010 at 10:10 am
I think this is a fundamental misunderstanding of dtsconfig files.
Config files can update properties of existing objects in a package, however, those properties have default values and if they didn't you would get errors at design-time. So those default values are being used. It doesn't copy your config file somewhere else "just in case". You can get it to read additional config files from the SQL job, but the lack of a config file will not fail a package and I can't see way to have it do it automatically. All I see is a way to supress the messagings of config warnings.
If you looked at the output from the execution I am willing to be it says that it couldn't find the config file.
CEWII
May 12, 2010 at 1:05 am
You are right, it is using default values (doh!), a total misunderstanding on my part, for all I know I'm still not 'getting it'. So...
I leave the variables blank when I create them, so that the config file can populate them, but I didn't realise that the package would keep those previously populated values as defaults, it's not really, imo, a 'true' default since it was at some stage populated by a config. Anyway, I would have prefered it to fail if a config file is not found. Thanks for the info!
Jinx.
May 12, 2010 at 8:51 am
Jinx-640161 (5/12/2010)
You are right, it is using default values (doh!), a total misunderstanding on my part, for all I know I'm still not 'getting it'. So...I leave the variables blank when I create them, so that the config file can populate them, but I didn't realise that the package would keep those previously populated values as defaults, it's not really, imo, a 'true' default since it was at some stage populated by a config. Anyway, I would have prefered it to fail if a config file is not found. Thanks for the info!
Jinx.
In most cases you can't leave the variables blank during the design stage, because it will fail validation.
I do have a thought on a way to force it to fail. Create a variable of type integer, set its value to zero (it should be anyway but I'm being careful). In each of your config files explicitly set that variable to 1. Add a Script task to the VERY TOP of the package (in order of precedence this should be first) that checks the value, if the value is 0 then fail the script task. Set the script task to fail the package if it fails. What happens then is that if the value isn't explicitly changed by a config the package WILL die.
Clear?
CEWII
May 12, 2010 at 8:55 am
Ah, good plan! I'll give it a try. 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply