2K8R2 SSIS Script Component is setting readonly variables?

  • Evil Kraig F

    SSC Guru

    Points: 100851

    So, I've got a confusion.

    I have a simple script component. The readonlyVariables line looks like so:

    User::overRideSettleDate,User::settledate

    The script itself looks like so:

    public void Main()

    {

    // TODO: Add your code here

    string rd = Dts.Variables["overRideSettleDate"].Value.ToString();

    if (rd.Equals("m/d/yyyy"))

    {

    Dts.Variables["settledate"].Value = DateTime.Today.AddDays(-1);

    }

    else

    {

    Dts.Variables["settledate"].Value = Convert.ToDateTime(rd);

    }

    }

    }

    }

    Somehow or another, this thing is able to set the settledate. I've confirmed it in a msgbox downstream that it's modified from default.

    Can anyone tell me why?! This is counter to everything I know about this.


    - 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

  • Evil Kraig F

    SSC Guru

    Points: 100851

    Just as a point of clarification, we had a corrupted package, thus the nonconformity to expectations.


    - 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

  • Eirikur Eiriksson

    SSC Guru

    Points: 182425

    Quick suggestions, open the package in a text editor and search for the variable name, there should only be one deceleration with that name but you might find another with a different scope.

    Secondly, always use the namespace when referencing variables in a script task (or anywhere rather)

    😎

  • Evil Kraig F

    SSC Guru

    Points: 100851

    Eirikur Eiriksson (11/17/2014)


    Quick suggestions, open the package in a text editor and search for the variable name, there should only be one deceleration with that name but you might find another with a different scope.

    Secondly, always use the namespace when referencing variables in a script task (or anywhere rather)

    😎

    Scope was confirmed, that wasn't the issue. Either way, writing onto a readonly variable as set at control flow object shouldn't occur no matter what the scope is.

    I've never, EVER, done anything like: DTS.Variables("Package.VariableName").value = localvar. It's never been necessary. I didn't even know you could! What's the syntax (perferably in VB)?


    - 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

  • Eirikur Eiriksson

    SSC Guru

    Points: 182425

    Evil Kraig F (11/17/2014)


    Eirikur Eiriksson (11/17/2014)


    Quick suggestions, open the package in a text editor and search for the variable name, there should only be one deceleration with that name but you might find another with a different scope.

    Secondly, always use the namespace when referencing variables in a script task (or anywhere rather)

    😎

    Scope was confirmed, that wasn't the issue. Either way, writing onto a readonly variable as set at control flow object shouldn't occur no matter what the scope is.

    I've never, EVER, done anything like: DTS.Variables("Package.VariableName").value = localvar. It's never been necessary. I didn't even know you could! What's the syntax (perferably in VB)?

    Did you search the package XML for another variable declaration?

    With the namespace, I'm referring to Dts.Variables["User::overRideSettleDate"].Value.ToString();

    😎

  • Evil Kraig F

    SSC Guru

    Points: 100851

    Yes, I did. Still have the old version. Nothing there, single declaration.

    Ah, that. The chioces are User:: and System::. Why would I wan't to include that inside the script? I have to at the object that allows access to them in the control flow, but it's overkill in the code. For the 20 or so system variables, just don't use those names (similar to not naming a column SELECT) and you're fine.


    - 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

  • Eirikur Eiriksson

    SSC Guru

    Points: 182425

    Evil Kraig F (11/17/2014)


    Yes, I did. Still have the old version. Nothing there, single declaration.

    Ah, that. The chioces are User:: and System::. Why would I wan't to include that inside the script? I have to at the object that allows access to them in the control flow, but it's overkill in the code. For the 20 or so system variables, just don't use those names (similar to not naming a column SELECT) and you're fine.

    The namespace is implicitly created when the variable is declared, no way of telling the difference between user::var and usre::var when you call var.

    Is the variable used somewhere else? Does the script use read/write variables?

    😎

  • Evil Kraig F

    SSC Guru

    Points: 100851

    Eirikur Eiriksson (11/17/2014)


    The namespace is implicitly created when the variable is declared, no way of telling the difference between user::var and usre::var when you call var.

    Okay, you have me confused. Please keep in mind this is 2k8 and I don't know if you can do some strangeness in 12/14. When you declare a variable on the left side, you get four options. Name, Scope, Data Type, and Default/Value. Anything generated in there is User::. How the heck would you end up with a Usre:: namespace (or anything else for that matter)?

    Is the variable used somewhere else? Does the script use read/write variables?

    It's in the XML 3 places. Once for the declaration. Once for the component in the ReadOnlyVariables section. Finally once in the script component body.

    The original idea was to read one variable, and write to another variable. Both of those variables have to be loaded to the readonlyvariable to be able to use them, else when the writing variable is put into ReadWriteVariables it errors. Something got screwy under the hood.


    - 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

  • Eirikur Eiriksson

    SSC Guru

    Points: 182425

    Evil Kraig F (11/17/2014)


    Eirikur Eiriksson (11/17/2014)


    The namespace is implicitly created when the variable is declared, no way of telling the difference between user::var and usre::var when you call var.

    Okay, you have me confused. Please keep in mind this is 2k8 and I don't know if you can do some strangeness in 12/14. When you declare a variable on the left side, you get four options. Name, Scope, Data Type, and Default/Value. Anything generated in there is User::. How the heck would you end up with a Usre:: namespace (or anything else for that matter)?

    Is the variable used somewhere else? Does the script use read/write variables?

    It's in the XML 3 places. Once for the declaration. Once for the component in the ReadOnlyVariables section. Finally once in the script component body.

    The original idea was to read one variable, and write to another variable. Both of those variables have to be loaded to the readonlyvariable to be able to use them, else when the writing variable is put into ReadWriteVariables it errors. Something got screwy under the hood.

    Didn't mean to cause confusion, seen stranger things happening in SSIS/BIDS, this can be considered more as a belt and suspenders type of approach 😉

    Just a quick though on the package XML, there is a GUID identifier for the variable, do the references to the variable match the appearance of the GUID in the XML?

    😎

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

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