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."
This seems to happen right at the start when the variables are processed before any tasks happen. It also only fails half the time.
I just don't even know where to begin. Any clues?
I also want to add that 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.
I think maybe the expression evaluator has a locking problem...
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 "DeleteRevenueFactQuery" failed evaluation. There was an error in the expression.
An error occurred with the following error message: "The expression for variable "DeleteRevenueFactQuery" 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 "@.[User::SqlServer]" on property "ServerName" cannot be evaluated. Modify the expression to be valid.
|||
Kris,
Its hard to work out what's wrong from what you've said here. Can you share your package?
If you like, send your package to me and I'l take a look.
-Jamie
jamie.thomson[at]conchango.com
|||
Kris, just a guess, but are you running the RTM version of SSIS?
Thanks,
K
|||Yes, I'm running on the final version of SSIS|||One thing to add, this package always works on a single processor machine but we can recreate the intermittent problem on 2 different multi-processor machines.
Does anyone know if expressions are evaluated to set variables in concurrent threads?
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.
Anyways, I think at this point I'm going to scrap using expressions and setup the variables and properties in a script task. I have to get this working soon.
Everything else is done on our project.
|||I too am experiencing this error. If I re-build/deploy the package, the error seems to go away until I build/deploy again, and then I usually get the error.
BobP
|||I had the same failure where I had a sequence container with 2 execute package tasks in it running in parallel. This worked fine on my workstation but failed on the dual processor server. I added a dependency so they run sequentially and the problem has gone away.
My failure condition on the dual proc server was when the SSIS packages were launched from a SQL Agent job. Running interactively with DTEXEC from a command proc worked.
|||I too have had the same problem on a dual-proc machine. There seems to be a familiar story developing here.
[Kris, sorry I didn't get back to you after you mailed - just haven't had time to explore it enough I'm afraid]
-Jamie
|||
I had the same problem on quad-processor machine. I'll try to get rid of this problem by setting MaxConcurrentExecutables to 1. It seems critical bug!
|||I am facing the same issue on the 4 proc machine, If I execute the package using dtexecui i works without any problems. Does anyone have resolution on this ?
Regards
Meghana
|||Well, I don't know what the current status on this matter is but, if anybody is interested, I'm having the same problems on a 4 Intel Xeon processors server.
Similar to what is mentioned in other related posts, everything runs smoothly on my 1 processor development box but, when deployed on a multiprocessors server, I'm having multiple DTS_E_VARIABLEALREADYONREADLIST error intermittently, i.e. with multiple tasks but not necessarily the same throughout various executions.
For the time being, I’ve fixed the problem by either adding precedence constraints between tasks (which could easily work in parallel) or by setting the MaxConcurrentExecutables package property to 1 or both but, for me, it is more a workaround than any real solution: if I cannot use both common configurations and multiprocessors, SSIS seems suddenly limited.
A quick note: contrary to what is mentioned in a related post, I can reproduce the same errors with both the dtexec command prompt utility and the graphical interface.
I should add that I’m also having issues with package transactions and Microsoft DTC but, at this point, I’m not sure whether these issues are due to multiprocessors, Windows 2000 (kinda old for SQL Server 2005, isn’t it?) or simply a wrong use of them.
Anyway, any new insights on this matter would be greatly appreciated.
No comments:
Post a Comment