October 24, 2011 at 8:07 am
Can you run an SSIS package from a SQL Agent job setting the packages variables by both an xml configuration file as well as setting a certain variable using Set Values by entering something like:
\package.variables[i]variablename[/i].Value
in property path and the value I want to change to in the Values column?
I basically want my package that has 20 variables listed in the configuration file to run with all the values in that xml file aside from a datetime variable which I want to run with today's date rather than the value in the config file.
Thought I may be able to do it this hoping that Set Values would take precedence over the configuration file.
October 24, 2011 at 9:16 am
Yes you can but what I am unsure about is order of precedence. If you happen to have the same variable in the config data will it overwrite the SET value and that I'm not 100% sure about, I was having trouble finding a list online. Within the package configurations its easy, you can order them.
While I can't find it now I seem to remember this order:
1. Default values in the packages.
2. SET values at start time
3. Pacakge configurations
4. Run-time values
However, if you have no overlap, then no problem..
CEWII
October 24, 2011 at 9:36 am
Elliott Whitlow (10/24/2011)
Yes you can but what I am unsure about is order of precedence. If you happen to have the same variable in the config data will it overwrite the SET value and that I'm not 100% sure about, I was having trouble finding a list online. Within the package configurations its easy, you can order them.While I can't find it now I seem to remember this order:
1. Default values in the packages.
2. SET values at start time
3. Pacakge configurations
4. Run-time values
However, if you have no overlap, then no problem..
CEWII
Aha. If that's the case, then my solution won't work as I would need the set values to take precedence over what was in the xml package configuration file. What do you mean by run-time values here by the way?
October 24, 2011 at 9:53 am
Griffster (10/24/2011)
Elliott Whitlow (10/24/2011)
Yes you can but what I am unsure about is order of precedence. If you happen to have the same variable in the config data will it overwrite the SET value and that I'm not 100% sure about, I was having trouble finding a list online. Within the package configurations its easy, you can order them.While I can't find it now I seem to remember this order:
1. Default values in the packages.
2. SET values at start time
3. Pacakge configurations
4. Run-time values
However, if you have no overlap, then no problem..
CEWII
Aha. If that's the case, then my solution won't work as I would need the set values to take precedence over what was in the xml package configuration file. What do you mean by run-time values here by the way?
I think Elliott is referring to changes which happen to the variables once the package is running.
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
October 24, 2011 at 10:11 am
I am. Once the package is running SET values and configurations are done.
CEWII
October 26, 2011 at 5:01 am
Ok, thanks both. Looks like I'm not going to be able to do this then if that's the order of precedence.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply