Script Task referencing its own properties

Question

Script Task referencing its own properties

Evil Kraig F

SSC Guru

Points: 100851
More actions
March 11, 2011 at 4:44 pm

#145108

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 Farrell
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

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246693 More actions · Answer 1

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!

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

chris.stuart SSCarpal Tunnel Points: 4965 More actions · Answer 2

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 ScriptMain

Inherits 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

Phil Parkin SSC Guru Points: 246693 More actions · Answer 3

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!

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

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 Farrell

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

Phil Parkin SSC Guru Points: 246693 More actions · Answer 5

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.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

chris.stuart SSCarpal Tunnel Points: 4965 More actions · Answer 6

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

COZYROC One Orange Chip Points: 28499 More actions · Answer 7

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/

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 8

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 Farrell

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

Phil Parkin SSC Guru Points: 246693 More actions · Answer 9

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.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

COZYROC One Orange Chip Points: 28499 More actions · Answer 10

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/

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 11

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 Farrell

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