“Failed to lock variable” SSIS error

Question

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...

Answer 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.

Answer 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 :)

Answer 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

Answer 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.

Answer 5

Another variation of this error: I copied a task from one package to another package. The task contained parameter mappings, but the source variable was not available in the new package. This made the parameter mapping invalid.

Answer 6

If the running version and build version is different also you may get this error. Ex I built the package on 2012 server and i ran it in 2016 and got this error.

Answer 7

I was also facing the same issue ,but now resolved this issue . I have used 2 foreachloop and 2 file system task in my package ,In First loop i'm assigning the values of excel fully qualified to a variable and same variable I was using in Second loop that is why this issue was coming. I have created another variable for second loop and assign that variable value into 2nd file system taks. Initially i was using only one variable as a source in both file system task,Now created 2 variable and issue is resolved. Please refer the sanpshot to see the details in snapshot.

Answer 8

Had the same error while debugging. Minimizing the visual studio, I saw pop-up with error message behind it. Clicking OK button rendered correct error in my visual studio. It was about missing column mapping. Worked after fixing it.