May 3, 2011 at 10:48 am
I'm a dba, I'm looking at some ssis packages written by former contractor developers in our system.
I have some questions about one of them.
For the package, there is a configuration file, dtsconfig, that defines some pacakge variables like:
<Configuration ConfiguredType="Property" Path="\Package.Variables[History::HistoryFolder].Properties[Value]" ValueType="String">
- <Configuration ConfiguredType="Property" Path="\Package.Variables[Notify::AdminEmails].Properties[Value]" ValueType="String">
....
Is this manually populated? I can understand User:: is for user defined variable, but what is the history:: and also notify:: varible?
My second question, in the ssis pacakge itself I can see a list of variable defined if I click variables.
Are the variables in config files are the same as those in the packages, which will overwrites the other?
Actually when I count the numbers of variables in config file and in ssis packages, the numbers are different, but I see some duplications there.
What the difference of varialbles in ssis pacakge and those in config file?
Thanks
Thanks
May 3, 2011 at 12:41 pm
The only variables that matter are those in the SSIS package itself.
They have a value set at design-time, which you can look at as some sort of default value.
You can change the value of a variable when a package runs (using expressions, using a script task/component, ...), but you can also overwrite the "default" value when the package starts.
This is done through package configurations and config files. In the config file, you specify the values that specific variables will receive when the package starts. This will overwrite the default value. If there is no configuration present in the config file for a variable, that variable will just keep it's default value when the package runs. If the config file tries to set a value to a variable that doesn't exist in the package, that configuration will just fail and generate a warning in your log. Beware, this doesn't fail your package.
Regarding the Notify:: and the History::, these are namespaces. You can assign a namespace to a variable, to divide them into logical groups. In the variables menu, there is a column that you can use to configure these, but by default this column is not shown.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 3, 2011 at 2:29 pm
Thank you, but a little bit confusing about below,
when you say, "the only variables that matter are those in the SSIS package itself."
But the variables in dtsconfig matter too, correct, because it will overwrite values that in package varible.
and are those varialbes in dtsconfig are manually entered into the xml file?
thanks
May 3, 2011 at 4:21 pm
For variables, there's two settings.
Design time and override. Design time is what you set them to when you first build the package. Override is what they're set to via command line switches, configurations, or code inside your package. All packages first boot up with their design time settings, and then any overrides are applied in a certain order.
In the case of configurations, only the configured value will matter, as it will override the design time setting before anything really gets going.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 3, 2011 at 11:46 pm
SQLMyFriend (5/3/2011)
Thank you, but a little bit confusing about below,when you say, "the only variables that matter are those in the SSIS package itself."
But the variables in dtsconfig matter too, correct, because it will overwrite values that in package varible.
and are those varialbes in dtsconfig are manually entered into the xml file?
thanks
Craig pretty much explains the whole concept, but to answer your question directly:
if a variable in the dtsconfig file configure a variable that exists in the package, it certainly matters, as it overrides the design time value.
If a variable in the dtsconfig file tries to configure a variable that doesn't exist in the package, it doesn't matter. It doesn't do anything. You cannot create extra variables in the dtsconfig file, only configurations.
They can be manually entered into the xml file (how are your xml typing skills? :-)), but the easiest way is to open the package configuration editor window (right click on control flow, choose package configurations). There you add a new configuration, choose XML, point to the config file and set the desired values for the corresponding variables.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 5, 2011 at 8:16 am
Koen Verbeeck (5/3/2011)if a variable in the dtsconfig file configure a variable that exists in the package, it certainly matters, as it overrides the design time value.
This is not 100% true in 2008 (2005 it is): locally in 2008, this is true. If the package is being executed via dtexec against a deployed package, config files are over-ridden by design-time values in the package at execution:
When you use the dtexec command prompt utility (dtexec.exe) to run a deployed package, the utility applies package configurations twice. The utility applies configurations both before and after it applies the options that you specified on command line.
As the utility loads and runs the package, events occur in the following order:
The dtexec utility loads the package.
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.)
The utility then applies any options that you specified on the command line.
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.
The utility applies the Parent Package Variable configurations.
The utility runs the package.
http://msdn.microsoft.com/en-us/library/ms141682.aspx
As with everything, it depends on the exact usage scenario, and the work-around for the above override issue is to use a config table, as opposed to an xml config file.
May 5, 2011 at 8:19 am
@Koen: learned this one after going through our SQL Server 2008R2 upgrade earlier this year ... all of our packages worked beautifully under 2005 with the xml files, but we ran into issues during our 2008 testing, and this override back to the internal/design package variable values was the culprit since we used dtexec.
May 5, 2011 at 11:19 am
@dg227: I think there is a mix-up between syntax here.
You are talking about configurations specified at design-time, I'm talking about actual values specified for variables in the variables window at design-time.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 5, 2011 at 12:39 pm
Koen-
Based on your second reply to the original post, you wrote:
"if a variable in the dtsconfig file configure a variable that exists in the package, it certainly matters, as it overrides the design time value"
What I was noting is that while this is true in 2005, in 2008 at run time (when using dtexec against a deployed package), the dtsconfig file variable will update/override the design time variable's value (in the Variables window when you edit the package in BIDS), but will then be restored back to the original designer/window variable's value.
Maybe there was mix-up in our contexts, but I wanted to point out to Old Hand that depending on your chosen execution method, deployment, and SQL Server/SSIS version, the final variable value, when using an xml config file, may not be the config file's value because of how 2008 loads at run time when using dtexec.
May 5, 2011 at 1:28 pm
Hmmm, maybe I'm reading it wrong, but I believe MSDN says the following:
"The utility then reloads the configurations that were specified in the package at design time ..."
It reloads configurations, not hard-coded variable values.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 5, 2011 at 1:45 pm
Koen-
The issue in 2008 is that (refer to the MSDN link that I noted earlier) is in using configuration files. Here's the deal:
2005-
Running a dtexec command line that uses a /Conf[igFile] switch to specify an xml configuration file will replace/overwrite the package variable values (when matched on the variable name). Here, at runtime, the package loads with the designer variable values, and then updates/overwrites matching variables from the config file with the file's values. Works great.
2008-
If you refer to http://msdn.microsoft.com/en-us/library/ms162810.aspx, it says:
"You can use the /ConfigFile option to load additional configurations at run time that you did not specify at design time. However, you cannot use the /ConfigFile option to replace configured values that you also specified at design time."
This is not entirely true in that in 2008 (not sold on the /SET in all cases), but the point here is that the load order has one additional step at execution via dtexec where the package/designer variable values are re-loaded after the config file is loaded, effectively resetting the variables back to their original, designer values, as saved in the deployed package:
http://msdn.microsoft.com/en-us/library/bb500430.aspx
Under the Behavior Changes Related to Package Configurations section ...
"When you use the dtexec utility (dtexec.exe) to run a deployed package, the sequence in which events occur has changed.
In SQL Server 2005 Integration Services, events occur in the following order:
The utility first applies the design-time configurations. (Design-time configurations are the configurations that you created and associated with the package when you designed the package in BI Development Studio.)
The utility then applies the run-time options that you specified on the command line when you started the utility.
In SQL Server 2008 Integration Services, events occur in the following order:
The utility first applies the design-time configurations.
The utility then applies the run-time options that you specified on the command line when you started the utility.
Finally, the utility reloads and reapplies the design-time configurations.
Because the events occur in this order, you can dynamically redirect a configuration by using the /SET command-line option to change the configuration string."
Work-arounds to this re-load of the original package's values are using the /SET (which I've limited success with when configs are involved) and, better, using the /CONN switch, pointing to a table configuration. This is probably the better fix, IMO.
May 5, 2011 at 1:54 pm
So it essentially boils down to this:
design-time configurations == design-time variable values?
I think not. I'm not 100% sure. I've used configurations in 2005 and 2008 a lot without any troubles, but that's probably because I avoided XML configurations as the plague 😀
Boy, I'm glad Denali will solve this mess once and for all.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 5, 2011 at 2:15 pm
Try it (those MS links say exactly that: the design-time value will be re-loaded over a config file's value when using dtexec execution of a deployed package):
1. (in SSIS 2008) create a package with a variable set to X ... output this variable name and value to a file, email, or whatever, so that you can verify it when executed
2. create/add a config file to the package with that variable, also set the value to X
3. save & deploy the package
4. after deploying the package (which has the saved value of X), change the config file so that the variable is now set to Y in the xml file
5. run dtexec on the command line, use the /CONFIG switch to point to that config file with the updated value of Y
You'd expect the output (file, email, whatever) to show the value of Y ... it'll be X. If you run the same package in BIDS (same config file and file value of Y), your output will be Y. The issue is with the dtexec command line and how it loads.
I haven't looked too closely at how Denali will handle this (if it maintains this configuration order or goes back to the 2005 order), but as we're now using config tables in most situations with deployed packages, I'm not too concerned. I'm just pointing this out to OP because it's not a logical change in terms of how dtexec handles config files when switching from 2005 to 2008, and may impact his original inquiry, depending on his version and environment.
If you can get the above scenario to work via dtexec using the /CONFIG switch and output Y, please let me know what you did .... I'd be curious to see it. 🙂
May 5, 2011 at 2:18 pm
And, upon thinking about it, maybe this is where the confusion is between us lol ... Here's my definitions:
Design-time = working in BIDS, looking at the variables window, etc.
Run-time = time of actual package execution, whether locally in BIDS, or via dtexec
May 5, 2011 at 2:25 pm
dg227 (5/5/2011)
Try it (those MS links say exactly that: the design-time value will be re-loaded over a config file's value when using dtexec execution of a deployed package):1. (in SSIS 2008) create a package with a variable set to X ... output this variable name and value to a file, email, or whatever, so that you can verify it when executed
2. create/add a config file to the package with that variable, also set the value to X
3. save & deploy the package
4. after deploying the package (which has the saved value of X), change the config file so that the variable is now set to Y in the xml file
5. run dtexec on the command line, use the /CONFIG switch to point to that config file with the updated value of Y
You'd expect the output (file, email, whatever) to show the value of Y ... it'll be X. If you run the same package in BIDS (same config file and file value of Y), your output will be Y. The issue is with the dtexec command line and how it loads.
I haven't looked too closely at how Denali will handle this (if it maintains this configuration order or goes back to the 2005 order), but as we're now using config tables in most situations with deployed packages, I'm not too concerned. I'm just pointing this out to OP because it's not a logical change in terms of how dtexec handles config files when switching from 2005 to 2008, and may impact his original inquiry, depending on his version and environment.
If you can get the above scenario to work via dtexec using the /CONFIG switch and output Y, please let me know what you did .... I'd be curious to see it. 🙂
I'll try it out when I have some spare time left. (wonder when that will be :-))
Denali makes it easier, as package configurations becomes obsolete (you can still use them, no worries, existing packages will not break). There is a whole new concept of how packages work together and how configurations (called parameters in Denali) are applied. Very very interesting stuff.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply