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

Answer 1

Hi Arun,

 

Out of interest, could you post your script please.

 

Cheers,

 

Answer 2

I have a variable test in my package..there is a single line in my script..

 

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

Answer 3

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,

 

Answer 4

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.

Answer 5

Same behavior under SQL Server 2008 - might be worth opening a bug on connect.microsoft.com

Answer 6

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,

 

Answer 7

Update this thread once the connect is open with that URL if you would.

Answer 8

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,

Answer 9

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

 

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

 

Answer 10

Confirmed on 9.00.3042 as well.

Answer 11

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

Answer 12

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?

 

Answer 13

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?

Answer 14

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.

Also note the documentation for the ReadOnlyVariables property (http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.tasks.scripttask.scripttask.readonlyvariables(SQL.90).aspx): 

"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.

Answer 15

I am pretty sure that it used to error out in the pre-RTM versions. Anyways, thanks for confirming this as a bug!

 

Arun

Answer 16

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,

Answer 17

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