March 14, 2011 at 3:42 pm
Quick question maybe someone can answer.
I currently use a script task to set the value of a variable. The script task works and sets the variable correctly.
However, if I run the data flows individually they will take the default/original value of the variable that was created.
Is there a way to set it so that the variable is stored and saved when I execute the script task (an option or something)? DTS would allow you to set the global variables by executing a script individaully and then store those values.
Let me know if that makes sense. I'm sure this has been covered but I've had a hard time wording it so searching is even more difficult.
Thanks.
March 14, 2011 at 3:47 pm
bruce.b.allen (3/14/2011)
Quick question maybe someone can answer.I currently use a script task to set the value of a variable. The script task works and sets the variable correctly.
However, if I run the data flows individually they will take the default/original value of the variable that was created.
Is there a way to set it so that the variable is stored and saved when I execute the script task (an option or something)? DTS would allow you to set the global variables by executing a script individaully and then store those values.
Let me know if that makes sense. I'm sure this has been covered but I've had a hard time wording it so searching is even more difficult.
Thanks.
Just to make sure I'm clear, a few questions. We're discussing the setting and retrieving of a global package variable, correct? If you look at the variables list on the left, it's scope is the same name as the package itself?
You've listed these variables in the ReadWriteVariables area in the Script's property settings window under 'Script', correct? Inside your script, you're using DTS.Variables("VName").value = "xyz", correct?
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
March 14, 2011 at 3:56 pm
Craig Farrell (3/14/2011)
bruce.b.allen (3/14/2011)
Quick question maybe someone can answer.I currently use a script task to set the value of a variable. The script task works and sets the variable correctly.
However, if I run the data flows individually they will take the default/original value of the variable that was created.
Is there a way to set it so that the variable is stored and saved when I execute the script task (an option or something)? DTS would allow you to set the global variables by executing a script individaully and then store those values.
Let me know if that makes sense. I'm sure this has been covered but I've had a hard time wording it so searching is even more difficult.
Thanks.
Just to make sure I'm clear, a few questions. We're discussing the setting and retrieving of a global package variable, correct? If you look at the variables list on the left, it's scope is the same name as the package itself?
You've listed these variables in the ReadWriteVariables area in the Script's property settings window under 'Script', correct? Inside your script, you're using DTS.Variables("VName").value = "xyz", correct?
Yes, that is all correct. I want to be able to save the new value to the variable I set in the DTS.Variables("Vname").Value and to have that value available when running tasks separately rather than at run time.
March 14, 2011 at 4:41 pm
bruce.b.allen (3/14/2011)
Yes, that is all correct. I want to be able to save the new value to the variable I set in the DTS.Variables("Vname").Value and to have that value available when running tasks separately rather than at run time.
Unfortunately, there is not, except to manually modify the design time value of the variable. It's metadata. Just like you can't modify file formats for flat files, you can't modify design time values of variables permanently in the package via scripting. You can only modify the runtime value.
If you're trying to merely test a section of your code, however, include the script task and the dataflow(s) in a sequence container, and execute only the sequence container.
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
March 15, 2011 at 1:11 am
Craig Farrell (3/14/2011)
bruce.b.allen (3/14/2011)
Yes, that is all correct. I want to be able to save the new value to the variable I set in the DTS.Variables("Vname").Value and to have that value available when running tasks separately rather than at run time.
Unfortunately, there is not, except to manually modify the design time value of the variable. It's metadata. Just like you can't modify file formats for flat files, you can't modify design time values of variables permanently in the package via scripting. You can only modify the runtime value.
If you're trying to merely test a section of your code, however, include the script task and the dataflow(s) in a sequence container, and execute only the sequence container.
To expand on the excellent reply of Craig:
you can look at the design-time values of variables as they are "default" values. If they are not changed by a configuration or expression, SSIS will use this value. You can only change the default value permanently by changing it manually.
If you need some variables to "hold state", you'll need to store them somewhere else, e.g. a table in the database or in a flat file, and read them back in when you start the SSIS package.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 15, 2011 at 2:23 am
The design-time, or default, variable values in a package can be modified in code from another package.
Here's a script task which opens a package, looks to see whether it contains a variable called "TestString" and if it does, modifies the value to "NewString" and saves the package.
public void Main()
{
Application a = new Application();
Package pkg = new Package();
pkg = a.LoadPackage(@"H:\Phil Documents\Visual Studio 2008\Projects\Samples\Samples\VariablesOnly.dtsx", null);
Variables pkgVars = pkg.Variables;
foreach (Variable pkgVar in pkgVars)
{
if (pkgVar.Name == "TestString")
{
pkgVar.Value = "NewString";
}
}
a.SaveToXml(@"H:\Phil Documents\Visual Studio 2008\Projects\Samples\Samples\VariablesOnly.dtsx", pkg, null);
Dts.TaskResult = (int)ScriptResults.Success;
}
This concept can, of course be expanded such that a master (or parent) package modifies its child packages before, or after, executing them.
March 15, 2011 at 2:27 am
Thanks for the code snippet Phil.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 15, 2011 at 8:05 am
Thank you Phil and Craig for you replies and examples. This helps a lot. The biggest reason I wanted to see the values is for troubleshooting what was actually passed and used. I'm betting I can use the debugger window to get thse to show their values when I run the the package.
Thanks again.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply