none
SSIS Script task parameters

    Question

  • Hello All,

     

    I have a variable which I pass into a Script task as a read only variable. But still I am able to overwrite the value of that variable within the script? Any reasons why?

     

    Thanks

    Arun

    Monday, September 22, 2008 11:14 AM

Answers

  • This issue should be present in the product since SQL 2005 release and we already have an open bug related to this issue.

    However because the posibility of this happening is rather low (only script tasks and custom components can be affected via using the Variable object returned by the VariableDispenser.LockForRead call) so is the priority of this bug now. We'll look into fixing this for future releases.

     

    Silviu Guea [MSFT],

    SQL Server Integration Services

    Thursday, September 25, 2008 11:02 PM
    Moderator

All replies

  • Hi Arun,

     

    Out of interest, could you post your script please.

     

    Cheers,

     

    Monday, September 22, 2008 11:30 AM
  • I have a variable test in my package..there is a single line in my script..

     

    Dts.Variables("User::Test").Value = 1

    Monday, September 22, 2008 11:33 AM
  • Hi,

     

    I can confirm that I can reproduce the problem :-)

     

    I wonder if an alternative access method, such as VariableDispenser, should be being used in order to access the variables.

     

    Take a look at the following article.

     

    http://www.developerdotstar.com/community/node/313

     

    Cheers,

     

    Monday, September 22, 2008 4:02 PM
  • Huh, that is interesting.  Initially, I would have expected that by dropping the variable into the ReadOnlyVariables collection, that it would have raised a syntax error by doing that.  Since it compiled fine, I was thinking maybe it'll raise a run-time error or merely not assign the change outside the scope of the script task but I'm batting .000 

     

    My initial value for var1 is 0, script updates it to 3 and the next task inline sees the value as 3.  A quick pass of Google didn't turn up anyone talking about this quirky behaviour.

    Monday, September 22, 2008 4:20 PM
    Answerer
  • Same behavior under SQL Server 2008 - might be worth opening a bug on connect.microsoft.com

    Monday, September 22, 2008 4:58 PM
    Moderator
  • Do you really think this is a bug?

     

    It surprises me that no one has come across this previously?

     

    I will go ahead and log a bug.

     

    Cheers,

     

    Monday, September 22, 2008 6:27 PM
  • Update this thread once the connect is open with that URL if you would.

    Monday, September 22, 2008 6:49 PM
    Answerer
  • Hi,

     

    I have created a bug ticket here.

     

    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=369187

     

    This is the first time I have raised a ticket so please provide input where appropriate and also verify that you too can reproduce the issue.

     

    Cheers,

    Monday, September 22, 2008 6:51 PM
  • Yes, I'd consider it a bug that you can put a variable in the ReadOnly list, and still write to it. That means that the variable is either being locked ReadWrite (which means it can't be accessed for read from another task at the same time), or it's being locked Read and you can still write to it, introducing potential threading issues. Either way, it's not good Smile

     

    I get the same behavior if I use the VariableDispenser, so that's not a workaround.

     

    Monday, September 22, 2008 7:15 PM
    Moderator
  • Confirmed on 9.00.3042 as well.
    Monday, September 22, 2008 7:31 PM
    Moderator
  • This is a limitation of the package variable locking behavior. Read-only and read-write locking only differ in how the lock on the variable is done not in what you can do with the variable when it's locked. For example a variable can only be locked for read-only access by multiple tasks successfully but only one task can lock it for read-write (and no read-only access is permited during the time) at one point in time.

     

    The script component is better in this regard because there we implement a property wrapper around the variable and only expose the getter when the lock is read-only but we expose the variable directly in the script task.

     

    Hope this helps,

    Silviu Guea [MSFT]

    SQL Server Integration Services

    Monday, September 22, 2008 8:22 PM
    Moderator
  • So, I can lock a variable for read-only access in three script tasks simultaneously, and write to it in all three script tasks? Which one wins?

     

    Monday, September 22, 2008 8:28 PM
    Moderator
  • Ah I think I understand.

     

    So locking in this context is referring to the access rights, between components, to the variable itself and not the data the variable holds?

    Monday, September 22, 2008 8:41 PM
  •  Silviu Guea [MSFT] wrote:

    This is a limitation of the package variable locking behavior. Read-only and read-write locking only differ in how the lock on the variable is done not in what you can do with the variable when it's locked. For example a variable can only be locked for read-only access by multiple tasks successfully but only one task can lock it for read-write (and no read-only access is permited during the time) at one point in time.

     

    The script component is better in this regard because there we implement a property wrapper around the variable and only expose the getter when the lock is read-only but we expose the variable directly in the script task.

     

    Hope this helps,

    Silviu Guea [MSFT]

    SQL Server Integration Services


    Note that I unmarked this as the answer because this is still an unresolved issue.


    "Gets or sets the comma-delimited list of existing variables made available to the package by the user for read-only access."

    I swear this used to error out as well, in previous versions, but I could be mistaken.
    Monday, September 22, 2008 9:10 PM
    Moderator
  • I am pretty sure that it used to error out in the pre-RTM versions. Anyways, thanks for confirming this as a bug!

     

    Arun

    Tuesday, September 23, 2008 5:13 AM
  • Hi Phil,

     

    So what happens now then? Will Microsoft look into the bug report or do more users need to qulify that it is an issue?

     

    How does the process typically work?

     

    Cheers,

    Tuesday, September 23, 2008 7:58 AM
  • This issue should be present in the product since SQL 2005 release and we already have an open bug related to this issue.

    However because the posibility of this happening is rather low (only script tasks and custom components can be affected via using the Variable object returned by the VariableDispenser.LockForRead call) so is the priority of this bug now. We'll look into fixing this for future releases.

     

    Silviu Guea [MSFT],

    SQL Server Integration Services

    Thursday, September 25, 2008 11:02 PM
    Moderator