Script Task referencing its own properties

  • Evil Kraig F

    SSC Guru

    Points: 100851

    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

  • Phil Parkin

    SSC Guru

    Points: 244578

    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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • chris.stuart

    SSCarpal Tunnel

    Points: 4965

    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: 244578

    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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Evil Kraig F

    SSC Guru

    Points: 100851

    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: 244578

    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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • chris.stuart

    SSCarpal Tunnel

    Points: 4965

    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

    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

    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: 244578

    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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • COZYROC

    One Orange Chip

    Points: 28499

    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

    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

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

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