Script Task referencing its own properties
-
I know this sounds a little nuts, but I can't find a method to get a script task to reference itself. For example, I'm trying to find the equivalent of MSGBOX( cstr( me.name)).
Me.<whatever> doesn't work, obviously, and I can't seem to find a way into the object tree that will reference the calling script's properties. I'm assuming that once I can find out how to get the name, I'll be able to dig in deeper and pull things out like the ReadVariable list that's on the object, or a value currently filled in by an expression.
At least, that's the hope. Let's start with a self-reference to the name, though, if anyone can. 🙂
Thanks folks.
- Craig FarrellNever 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 -
While it seems that there is a perfectly good variable waiting for you to access ("System::TaskName") (which I can see in my Variables window), this seems not to be available during execution. Here's some code which should work:
public void Main(){
Package pkg = new Package();
Variables vars = null;
VariableDispenser variableDispenser = pkg.VariableDispenser;
variableDispenser.LockOneForRead("System::TaskName", ref vars);
variableDispenser.GetVariables(ref vars);
bool fireAgain = true;
foreach (Variable myVar in vars)
{
Dts.Events.FireInformation(0, "Value", myVar.Value.ToString(), "", 0, ref fireAgain);
}
vars.Unlock();
Dts.TaskResult = (int)ScriptResults.Success;
}
But when I run it, I get the following error:
Error: 0x1 at Script Task: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: Failed to lock variable "System::TaskName" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".---> System.Runtime.InteropServices.COMException (0xC0010001): Failed to lock variable "System::TaskName" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".
I'm not sure where to go next with this one!
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 -
Phil Parkin (3/13/2011)
While it seems that there is a perfectly good variable waiting for you to access ("System::TaskName") (which I can see in my Variables window), this seems not to be available during execution. Here's some code which should work:public void Main(){
Package pkg = new Package();
Variables vars = null;
VariableDispenser variableDispenser = pkg.VariableDispenser;
variableDispenser.LockOneForRead("System::TaskName", ref vars);
variableDispenser.GetVariables(ref vars);
bool fireAgain = true;
foreach (Variable myVar in vars)
{
Dts.Events.FireInformation(0, "Value", myVar.Value.ToString(), "", 0, ref fireAgain);
}
vars.Unlock();
Dts.TaskResult = (int)ScriptResults.Success;
}
But when I run it, I get the following error:
Error: 0x1 at Script Task: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: Failed to lock variable "System::TaskName" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".---> System.Runtime.InteropServices.COMException (0xC0010001): Failed to lock variable "System::TaskName" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".
I'm not sure where to go next with this one!
Jsut maybe : I'm using the following in the Script component It works for the update of a variable whilst I'm doing the transform, maybe look into rather doing it in the script component instead of the script task?
Public Class ScriptMainInherits UserComponent
Dim dDate As Date
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim Utils As New ASD.ConvertToCorrectDataTypes
Dim vars As IDTSVariables100
........
Me.VariableDispenser.LockOneForWrite("CurrentDateBeingRead", vars)
vars(0).Value = dDate
vars.Unlock()
ElseIf sFirstChar = "B" Then
........
End Sub
End Class
-
Quote from Technet about IDTSVariables100:
This API supports the .NET Framework infrastructure and is not intended to be used directly from your code.
See here.
Also, the system variable you have accessed is not the name of the script!
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 -
Thanks Chris, but I agree with Phil, I'm trying to avoid using system level functions, as the framework functions aren't promised to be the same (afaik) between .NET versions and I don't want to recode this on a 2k8 upgrade.
Phil, I ran into that headache too. I'm relatively sure the system variables aren't exposed at all to any of the components directly, as they're used as settings for the objects holding the components. For example, try setting propogate in an event handler on the fly, it no workie. I might be able to custom task to read that system variable, however.
Phil Parkin (3/13/2011)
I'm not sure where to go next with this one!My <headdesk> exactly! Well, I appreciate the attempt.
- Craig FarrellNever 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 -
Craig Farrell (3/14/2011)
My <headdesk> exactly! Well, I appreciate the attempt.
No problem. There is very limited documentation around to explain which variables are 'hidden' and which are not.
If you set a breakpoint on pre-execute of the script you'll see the TaskName variable there, happily displaying its contents in a mocking fashion 🙂 The fact that you can't seem to get to that in code is a wind-up. Please post back if you find a way.
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 -
Hi guys.
It was only a suggestion, in no way intended as an answer:-)
the variable I was updating the the script was a user variable but I was thinking that you might be able to access the system variables in a similar way.
And thanks for the link, I see what they say about IDTSVariables100, but it was a solution to my problem.:-D
-
Guys,
I did the following:
- defined TaskName in ReadOnlyVariables input.
- called the following code from script:
MsgBox(Dts.Variables("TaskName").Value)And it worked, showing script task's name. I think it is incorrect to use System:: in front of variables (system or not).
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
CozyRoc (3/16/2011)
Guys,I did the following:
- defined TaskName in ReadOnlyVariables input.
- called the following code from script:
MsgBox(Dts.Variables("TaskName").Value)And it worked, showing script task's name. I think it is incorrect to use System:: in front of variables (system or not).
Thanks Cozy, but you missed the point. I'm looking for something that can be run in any task and read it's own name, or ReadOnlyVariable List, or whatever. I'm trying to figure out how to self-reference in a script object.
- Craig FarrellNever 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 -
OK, simpler version:
public void Main(){
Variables vars = null;
Dts.VariableDispenser.LockOneForRead("System::TaskName", ref vars);
MessageBox.Show(vars[0].Value.ToString());
vars.Unlock();
Dts.TaskResult = (int)ScriptResults.Success;
}
It is possible to declare a User variable with the same name as a System variable - I tried with User::MachineName. In that instance, an unqualified call to "MachineName" returns "System::MachineName": it is necessary to call "User::MachineName" explicitly to return the value in there.
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 -
Craig,
The properties you are referring to are contained in the so called "script host", which is the actual custom component managing the script object. My question is why would you want access to these properties? For diagnostic information?
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
Phil Parkin (3/17/2011)
OK, simpler version:public void Main(){
Variables vars = null;
Dts.VariableDispenser.LockOneForRead("System::TaskName", ref vars);
MessageBox.Show(vars[0].Value.ToString());
vars.Unlock();
Dts.TaskResult = (int)ScriptResults.Success;
}
It is possible to declare a User variable with the same name as a System variable - I tried with User::MachineName. In that instance, an unqualified call to "MachineName" returns "System::MachineName": it is necessary to call "User::MachineName" explicitly to return the value in there.
Phil, you're awesome. Thank you. My life got nuts so I wasn't able to come back to this yet, so I appreciate the time you took to research this for me.
@Cozy: Dead on target. I'm trying to create a generic object for diagnostic and logging usage, also for an article I'm intending to write. By creating an object once that will allow for this and giving them generic code to populate them all with, it will be easier to walk people through the process if they can just copy, paste, rename, and set the variables properties.
- Craig FarrellNever 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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply