MS SQL Server DTS - Intermittent Failure on Expression Evaluation

Author

Intermittent Failure on Expression Evaluation

Kris Selden

2006-01-12, 8:24 pm

We have a SSIS package that intermittently fails on the deployed server
(works fine on our dev server) with the following error:

"The variable "%1!s!" is already on the read list. A variable may only be
added once to either the read lock list or the write lock list."

Not only does it fail only some of the time it also fails on different
variables, always on an expression and always at the begining.

The variables that are giving the intermittent problems are used in more
than one expression to initialize other variables or properties.

I'm worried it is executing the expressions on more than one thread and
ending up on different processors, and the locking scheme doesn't use a
memory boundary when checking if the variable is already on the readonly list
before adding it.

Here are some examples:

The following is the *first* 4 messages of one failed run:
Beginning of package execution
The variable "User::RefreshFrom" is already on the read list. A variable may
only be added once to either the read lock list or the write lock list.
The expression for variable " DeleteRevenueFactQue
ry" failed evaluation.
There was an error in the expression.
An error occurred with the following error message: "The expression for
variable " DeleteRevenueFactQue
ry" failed evaluation. There was an error in
the expression.".

The following is the *first* 3 messages of another failed run:
Beginning of package execution
The variable "User::SqlServer" is already on the read list. A variable may
only be added once to either the read lock list or the write lock list.
The expression "@& #91;User::SqlServer]
" on property "ServerName" cannot be
evaluated. Modify the expression to be valid.

Kris Selden

2006-01-27, 11:23 am

I was under the impression that as an MSDN subscriber, I was going to get a
response to this in 2 days.

"Kris Selden" wrote:

> We have a SSIS package that intermittently fails on the deployed server
> (works fine on our dev server) with the following error:
>
> "The variable "%1!s!" is already on the read list. A variable may only be
> added once to either the read lock list or the write lock list."
>
> Not only does it fail only some of the time it also fails on different
> variables, always on an expression and always at the begining.
>
> The variables that are giving the intermittent problems are used in more
> than one expression to initialize other variables or properties.
>
> I'm worried it is executing the expressions on more than one thread and
> ending up on different processors, and the locking scheme doesn't use a
> memory boundary when checking if the variable is already on the readonly list
> before adding it.
>
> Here are some examples:
>
> The following is the *first* 4 messages of one failed run:
> Beginning of package execution
> The variable "User::RefreshFrom" is already on the read list. A variable may
> only be added once to either the read lock list or the write lock list.
> The expression for variable " DeleteRevenueFactQue
ry" failed evaluation.
> There was an error in the expression.
> An error occurred with the following error message: "The expression for
> variable " DeleteRevenueFactQue
ry" failed evaluation. There was an error in
> the expression.".
>
> The following is the *first* 3 messages of another failed run:
> Beginning of package execution
> The variable "User::SqlServer" is already on the read list. A variable may
> only be added once to either the read lock list or the write lock list.
> The expression "@& #91;User::SqlServer]
" on property "ServerName" cannot be
> evaluated. Modify the expression to be valid.
>
>

Darren Green

2006-01-28, 7:23 am

Have you registered your posting alias in the MSDN Subscriber section?
If not then you won't expect a response as they do not know who you are.

"The variable "User::RefreshFrom" is already on the read list. A
variable may only be added once to either the read lock list or the
write lock list." - This error I have seen several times when trying to
run multiple tasks/containers in parallel. Forcing them to run serially
by using precedence constraints has always solved the problem. You may
also try controlling the threads with the MaxConcurrentExecuta
bles
property of the package.

I am certain there is a bug here, but I've never had the time to
investigate seriously and log it.

--
Darren
http://www.sqldts.com
http://www.sqlis.com

Sponsored Links