SSIS Variable Lock Error
I am getting an error randomly for a scheduled SSIS package that runs hourly. The funny thing is, if I delete the checkpoint file and run the package again, it works fine, but the error may appear in the future. I don't know why this is happening. Here is the complete error message.
Runs as user: UserNameChanged. Executing SQL Server (R) Microsoft Batch 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 and Retrieve Date
Description: Failed to lock the variable "User :: UpdateProcessControlQuery" for read access with error 0xC0010001 "The variable could not be found. This occurs when an attempt is made to retrieve a variable from the variable collection on the container at run time and the variable is not there. Variable name may have changed or the variable is not created. ". Error termination error: 2010-06-24 09: 21: 45.84 Code: 0xC0024107 Source: Save MaxLSN & Issue Date Description: There were errors while checking the task. DTExec termination error: batch execution returns DTSER_FAILURE (1). Started: 09:21:40 Finished: 09:21:45 Elapsed: 4.875 seconds. Package execution failed. The step failed.
To add to that, I have over 100 of these packages running in groups of 20 packages per group and 5 groups per hour. And this lock variable error appears at least once in every loop. So I need to figure out the root cause. Can anyone help ...
source to share
I suspect this is because the same variable is being written to multiple components that run in parallel. Basically, it will be a race condition for this variable.
For example, if component A and component B can run in parallel and both are written to a given variable, then it is possible that component A will write to it (showing it unavailable) when component B tries to execute. Since the start time of the two jobs will vary between runs, you may end up with cases where the batch looks random.
To fix the problem, you either need to add an extra variable so that it is not shared, or alternatively, force one component to finish before starting the other (via a dependency).
Hope it helps.
source to share
I had the same problem. The fix that worked for me:
The root cause . I created a variable and used it inside the "data stream" to store the "Row Count" value. After that I tried to use this variable in "control flow"> "Script Task". It didn't work and gave me the same error as the message.
Correction: I created variables and used them in the Script Task. "After it was used in the script task, I assigned the Row Count value in the dataflow> Row Count.
It seems strange to me, but it worked :)
source to share
I also faced the same problem, but now I have solved this problem. I used 2 foreachloop tasks and 2 filesystem tasks in my package. In the first loop, I am assigning Excel values that completely match the variable and the same variable that I used in the second loop, which is why this problem occurred. I created another variable for the second loop and assigned its value at the 2nd clock cycle of the filesystem. Originally I only used one variable as source in filesystem task, now created 2 variables and the problem is solved. Please refer to sanpshot to see details in the shot.
source to share