Error with for..next loop variable

I'm setting a variable called ConnectionString_vc in a for..next loop. The connectionstring property of one of my connections is then set to @.[User::ConnectionString_vc] via an expression. When running the package in a SQL agent job I'm getting the following error in my logs. I've received this sporadically when running it in debug mode in BIS, but it happens consistently in Agent.

The variable "User::ConnectionString_vc" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.

Are you using that variable anywhere else? Perhaps in a script task?

It sounds as tho you've got the variable in the ReadOnlyvariables or ReadWriteVariables property of a script task and you're also calling LockOneForRead or LockOneForWrite within the script task.

-Jamie|||Not using the variable in a script task in this package. Control flow looks like this...

1. Execute SQL task queries list of connection strings that are stored in destination database. (Package basically loads data from multiple (identical) sources into one single destination. Just different instances of the same legacy system.) Execute SQL task maps Result set to variable called User::ADORecords.

2. Next step: For loop. Enumerator is "Foreach ADO Enumerator", ADO object source variable is "User::ADORecords", Enumeration mode is "Rows in the first table". Variable mapping is set for User::ConnectionString_vc

3. Within for loop there are about a dozen dataflow tasks that all reference a connection which has its connectionstring property equal to
@.[User::ConnectionString_vc] via an expression.

It runs successfully in debug mode or even when running without debugging from BI Studio. But when run from SQL Server agent I get the error.|||The issue doesn't seem to be related to the setting of the variable's value. It seems to be related to the Connection's connectionstring property being defined in the expression. I removed the expression so that as the For Loop ran it would set the variable but the connection would always be based off the initial default value of the connection instead of the expression. It ran through successfully.

Can anyone think of a better way to loop through a resultset of connection strings and modify the connection to the source system?

Thanks.|||That's bizarre. It sounds as though you've done it exactly as best practice dictates. Can you share the package?

This is annoying. It SHOULD work.

-Jamie|||

I'm having a similar problem. I'm using variables in expressions to set properties and also adding to the ReadOnlyList. I sometimes get that message even though I'm not doing any manual locking from the SQL agent but never get it in debug mode.

It also seems to happen when the expression is evaluated. It definately happens before any tasks even have run!

Anyways did you ever solve this?

|||

Kris, I'm doing this in a few packages as well and I experienced the problem when the variable that I passed in through the expression didn't exist. I changed over to a VariableDispenser and used the contains property in the script to do a check to make sure it existed before I did anything with it.

Brian Knight
Blog: http://www.whiteknighttechnology.com

|||

Are you using a script to build the value of the variables instead of using variables set by expressions?

Or are you using the VariableDispenser to just ensure it exists before continuing to other tasks?

|||

I wanted to have a general repository for variables (package configuration with steroids) so we had all the variables in a table. Then there's a stored procedure to retreive the variables for a group of package and inside a ForEach loop the Script task dynamically assigned the variables (so the variable name and value were dynamic from the table). I started much like you by using an expression to dynamically push the variable name into the ReadWriteVariables option in the script task based on the record in the ForEach collection. After seeing this bomb due to variables not matching (my recordset had a variable that didn't exist in the package), I switched to using the VariableDispenser. That way I could do a contains check to see if the value existed in the package and try catch it.

I'm sure you know this already, but here is how I set the variables using the VariableDispenser minus the error handling I added later:

http://whiteknighttechnology.com/cs/blogs/brian_knight/archive/2005/12/30/57.aspx

I hope I'm reading your question correctly though! Pardon if I went in a different direction than your question.

-- Brian