“Failed to lock variable” SSIS error

问题:

I am getting an error randomly for a scheduled SSIS package which runs hourly. The funniest thing is that if I delete the checkpoint file and run the package again it works fine but the error may show up at a future run. I have no clue why this is happening. Here is the full error message.

Executed as user: UserNameChanged. Microsoft (R) SQL Server Execute Package Utility Version 10.0.2531.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 09:21:40 Error: 2010-06-24 09:21:45.83 Code: 0xC0014054
Source: Save MaxLSN & Extract Date
Description: Failed to lock variable "User::UpdateProcessControlQuery" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.". End Error Error: 2010-06-24 09:21:45.84 Code: 0xC0024107 Source: Save MaxLSN & Extract Date Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 09:21:40 Finished: 09:21:45 Elapsed: 4.875 seconds. The package execution failed. The step failed.

To add to this, I have 100+ such packages running there in groups of 20 packages in a group and 5 groups in an hour. And this 'variable lock' error is popping up at least once in every cycle. So I need to find out the root cause. Can anyone help...

回答1:

I'd suspect that this is related to the same variable being written to in multiple components that execute in parallel. Essentially this would be a race condition on that variable.

For example if Component A and Component B can execute in parallel and both write to a given variable then it is possible that Component A will be writing to it (rendering it unavailable) when Component B tries to execute. As the start time of the two jobs will vary between runs you can get cases where the package appears to fail randomly.

To resolve the issue you would need to either add an additional variable so that they are not shared or alternatively force one component to complete before the other begins (through a dependency).

Hope this helps.

回答2:

I had the same problem. Fix which worked for me:

Root Cause: I had created the variable and used it inside "Data Flow" to hold value of "Row Count". After this, I was trying to use this variable in "control Flow" > "Script Task". It did not work and gave me same error as posted.

Fix: I created the variables and used in "Script Task". After it got used in script task, I assigned the value of Row Count in Data Flow > Row Count.

It looks strange to me, but it worked :)

回答3:

It seems there is a variable is missing to the sequence container in the package may it needs to get from either other jobs or parent package. I think its the problem of either scope of the variable or the generation of variable values at run time.

thanks prav

回答4:

Check and ensure the connection manager properties in tasks editor. If you copy the tasks/ connection manger from other packages then we must ensure all the properties have been re-defined correctly.