SSIS间歇性变量错误:系统找不到指定的文件 [英] SSIS Intermittent variable error: The system cannot find the file specified

问题描述

我们的 SSIS 将结构化为一个控制包和许多从控制包调用的子包(大约 30 个).子包通过执行包任务调用.每个子包有一个执行包任务.每个执行包任务使用文件连接管理器来指定子包 dtsx 文件的路径.每个子包有一个文件连接管理器.每个文件连接管理器都有一个为 ConnectionString 属性定义的表达式.这个表达式看起来像这样:

Our SSIS pacakges a structured as one Control package and many child packages (about 30) that are invoked from the control package. The child packages are invoked with Execute Package Task. There is one Execute Package Task per child package. Each Execute Package Task uses File Connection Manager to specify path to the child package dtsx file. There is one File Connection Manager per child package. Each File Connection Manager has an expression defined for ConnectionString property. This expression looks like this:

@[Template::FolderPackages]+"MyPackage.dtsx" 

每个包的文件名都不同.变量 (FolderPackages) 在 SSIS 包配置文件中指定.

The file name is different for each package. The variable (FolderPackages) is specified in the SSIS package configuration file.

运行时产生的错误是

加载包文件MyPackage.dtsx"时出现错误 0x80070002

Error 0x80070002 while loading package file "MyPackage.dtsx"

系统找不到指定的文件."失败的包因运行而异，有时根本没有包失败.这是在完全相同的环境/数据等上运行时.

The system cannot find the file specified." The package that fails is different from run to run and sometimes no packages fail at all. This is when run on exactly the same environment/data etc.

我在此错误期间运行 FileMon 并发现当错误发生时，SSIS 尝试从错误的位置读取 dtsx 文件，即从 system32.我检查了这是否与 @[Template::FolderPackages] 变量为空时会发生的情况相同，但是因为每个子包都使用相同的变量并且对某些包有效，但有时对其他包不起作用，所以我没有对这一事实的解释.

I ran FileMon during this error and found out that when the error happens SSIS tries to read the dtsx file from a wrong place, namely from system32. I checked that this is identical to what would happen if @[Template::FolderPackages] variable were empty, but because the very same variable is used for every child package and works for some but doesn't work sometimes for others, I have no expalnation to this fact.

有什么明显的事情，或者是时候向 Microsoft 拨打支持电话了吗?

Anything obvious, or time to raise a support call with Microsoft?

推荐答案

这个问题的官方回答是 SQL 2005 和 2008 的一个 bug.很多任务访问同一个变量会导致竞争条件，有些任务得到表达式的默认值而不是计算值.

The official answer to this issue is that it is a bug in SQL 2005 and 2008. Many tasks accessing the same variable cause a race condition, and some tasks get the default value for the expression instead of the evaluated value.

解决方法是确保默认值(在属性表中为您遇到问题的任何属性定义的值)应该是适用于您的生产环境的值.

The workaround is to ensure that the default value (the value defined in the property sheet for whatever property you are having trouble with) should be the value that will work in your production environment.

这样，当 prod 中发生竞争条件时，SSIS 将回退到包值，这仍然有效.

This way, when the race condition happens in prod, SSIS will fall back to the package value, which will still work.

在开发中?好吧，在我们从 Microsoft 获得错误修复之前，您将不得不手动处理该问题.

In dev? Well you're just going to have to deal with that manually until we get a bug fix from Microsoft.

这篇关于SSIS间歇性变量错误:系统找不到指定的文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！