ERROR: A variable may only be added once to either the read lock list or the write lock list.

Hi,
I have set of 2 DTS packages, one of which calls the other by forming a command-line (dtexec) using a Execute Process task.

From the parent package-> Execute Process Task->
dtsexec /F etc... /<pkg variable> = "servername"

Each of the parent and the called package have a variable: "User::DWServerSQLInstance" which is mapped to the SQL server connection manager server name property using an expression. The outer package has the above variable and so does the inner called package (which gets assigned through the command line from the outerpackage call to inner)

I "sometimes" get the following error:

OnError,I4,TESTDOM\Administrator,ACDWAggregation,{A1F8E43F-15F1-4685-8C18-6866AB31E62B},{77B2F3C7-6756-46EB-8C01-D880598FB4B3},5/22/2006 5:10:28 PM,5/22/2006 5:10:28 PM,-1073659822,0x,The variable "User::DWServerSQLInstance" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.

Help would be appreciated!

I have seen other posts on this but, not able to relate the solution to my scenario.

Do you have multiple tasks (in the same package) executing in parallel, and using same variable, e.g. in property mappings?

If yes, I think this problem is fixed by this post-SP1 fixes collection:
http://support.microsoft.com/Default.aspx?id=918222

Alternatively, you may change the package so the tasks are executed sequentially by creating precedence constraints.|||

Hi Brian,
The child Package uses the User Variable: DWSQLInstanceName mentioned above to initialize 4 SQL Server connections (2 OLEDB & 2 ADO .NET) -- Only <<Server Name>> property using expressions.

I also checked that this error seems to be only on Multi-proc machine. Don't get the same on my laptop.

I cannot seralize the tasks since, the error is coming @. package level while evaluating the package expressions (i.e. for the connections) when the package starts.

What is the best way to still use expressions for connection server names but, get around this error?
Is this what is fixed in SP1?

Also, it seems that the packages does not fail when it encounters the error which I am getting. I have MaxErrorCount = 1 for Package. The package goes ahead.

|||I believe the issue with connections sharing the variable is fixed in the commulative hotfix package I've mentioned above. It should be installed on top of SP1: first, install SP1 then install all the component packages from the hotfix package in the order they are listed in the article.

http://www.microsoft.com/downloads/details.aspx?FamilyId=CB6C71EA-D649-47FF-9176-E7CAC58FD4BC
and then
http://support.microsoft.com/Default.aspx?id=918222|||This was indeed a bug and the post SP1 hotfix "fixes" it.

However, you can still create the bug. It's harder but stil there...|||

Thanks Michael and Crispin for the help on this!

I think we will need to upgrade to SP1 and install the Hotfix bundle for SP1. I think this should fix the issue which I am facing i.e. use of same variable for setting connection "Server Name" - Property in the expressions.

Thanks,
Gaurav

Error: 'SubQuery Returned More than 1 Value'

I have some code that calls a stored procedure on SQL Server 2005 using the Microsoft JDBC driver 1.1. The code normally works however, every once in a while an exception is thrown:

Code Snippet

com.microsoft.sqlserver.jdbc.SQLServerException: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !, <,<=, >, >=, or when the subquery is used as an expression.

Generally, this has been resolved by restarting SQL Server 2005, but why is it showing up to being with?

Stored Procedure:

Code Snippet

ALTER Procedure [dbo].[addRecord]
@.userID int,
@.itemID int,
@.info varchar(50),
@.comment varchar(50),
@.output int output
AS

Declare @.dateSubmitted datetime
set @.dateSubmitted = getDate();

--Insert the new record. THIS TABLE has an ID identity Primary Key
--column that auto-increments.
insert into RecordTable
(UserPerson, Information, DateSubmitted)
values (@.userID, @.info, @.dateSubmitted);

Get the ID Assigned in the record table. The Item table
has a Foreign key on this column.
Declare @.assignedID int
set @.assignedID = (select ID from RecordTable where DateSubmitted = @.dateSubmitted)

/**Set the output parameter.*/
set @.output = @.assignedID;

--Now update the Item Table.
insert into Item
(ID, RecordID, Comment)
values (@.item,@.assignedID, @.comment);

set @.assignedID = (select ID from RecordTable where DateSubmitted = @.dateSubmitted)

my guess is that you get that error because you insert more than one record with the same datesubmitted value.

|||

CharlieXXX wrote:

set @.assignedID = (select ID from RecordTable where DateSubmitted = @.dateSubmitted)

my guess is that you get that error because you insert more than one record with the same datesubmitted value.

That's not possible. The error occurs even when the RecordTable is empty.
|||

SomeDeveloperPerson wrote:

CharlieXXX wrote:

set @.assignedID = (select ID from RecordTable where DateSubmitted = @.dateSubmitted)

my guess is that you get that error because you insert more than one record with the same datesubmitted value.

That's not possible. The error occurs even when the RecordTable is empty.

Charlie... you're actually correct. I modified the line to:

Code Snippet

set @.assignedID = (select MAX(ID) from RecordTable where DateSubmitted = @.dateSubmitted)

And it works with no problems now. Apparently the code is executing faster than expected so that several records are being written in under a millisecond.
|||

You can reduce the 'effort' of the procedure with this simple alteration:

Instead of having another query to obtain the IDENTITY value of the just entered row, use the SCOPE_IDENTITY() function.

Code Snippet

--Insert the new record. THIS TABLE has an ID identity Primary Key
--column that auto-increments.
insert into RecordTable
(UserPerson, Information, DateSubmitted)
values (@.userID, @.info, @.dateSubmitted);

Declare @.assignedID int
set @.assignedID = SCOPE_IDENTITY()

It saves a small amount of unnecessary server 'work' since the SCOPE_IDENTITY() is part of the return information from the original insert.

And using MAX() in the fashion that you are could potentially have you obtaining the value from a row inserted by another user. Not a very reliable prospect.

Error while executing SSIS package from other SSIS package

Hi,

In our project we have two SSIS package.

And there is a task (Execute SSIS package) in First package that calls the execution of second package.

I m continuously receiving an error "Failed to decrypt protected XML node "PackagePassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available."

As we are running first package by job, job runs successfully logging above error

The protection level of second package is set to "EncryptSensitiveWithUserKey"

Can anybody please suggest how to handle it?

If these DTSX has been saved on the MSDB you could use 'Server Storage' as protection level.

EncryptSensitive.. is the default protection level for the packages.

|||

If protection level property of the package has EncryptSensitive WithUSerKey means that only the author of the package is allowed to run it. This KB offers diffrent alternatives to solve your issue:

An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step:

http://support.microsoft.com/kb/918760

|||

Admin,

This topic is a good candidate for a "sticky" for the forum.

This question is asked once a day... we could come up with a synopsis on package encryption

|||

The DTSX is not saved in the MSDB,

its at disk only.