“无法锁定变量"SSIS错误 [英] "Failed to lock variable" SSIS error

问题描述

我随机收到一个每小时运行一次的预定 SSIS 包的错误.最有趣的是，如果我删除检查点文件并再次运行该包，它可以正常工作，但在以后的运行中可能会出现错误.我不知道为什么会这样.这是完整的错误消息.

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.

以用户身份执行:UserNameChanged.Microsoft (R) SQL Server 执行包实用程序版本 10.0.2531.0对于 64 位版权 (C) Microsoft公司 1984-2005.保留所有权利.
开始时间:09:21:40 错误:2010-06-2409:21:45.83 代码:0xC0014054
来源:Save MaxLSN &提取日期
无法锁定变量."User::UpdateProcessControlQuery" 为读取访问错误 0xC0010001 The找不到变量.出现这种情况当试图检索一个Variables 集合中的变量在执行期间在容器上包，并且变量不是那里.变量名可能有已更改或变量未被创建.".结束错误错误:2010-06-24 09:21:45.84 代码:0xC0024107 来源:保存 MaxLSN &提取日期说明:有是任务验证期间的错误.结束错误 DTExec:包执行返回 DTSER_FAILURE (1).开始:09:21:40 结束:09:21:45已用时间:4.875 秒.包执行失败.步骤失败.

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.

此外，我有 100 多个这样的包在那里以 20 个包为一组运行，每小时运行 5 个包.这个变量锁定"错误在每个周期中至少出现一次.所以我需要找出根本原因.谁能帮忙...

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

推荐答案

我怀疑这与在并行执行的多个组件中写入的同一变量有关.本质上，这将是该变量的竞争条件.

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.

例如，如果组件 A 和组件 B 可以并行执行并且都写入给定变量，那么当组件 B 尝试执行时，组件 A 可能会写入(使其不可用).由于这两个作业的开始时间在运行之间会有所不同，因此您可能会遇到包似乎随机失败的情况.

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

希望这会有所帮助.

这篇关于“无法锁定变量"SSIS错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！