ValidateExternalMetadata 属性，这到底是做什么的? [英] ValidateExternalMetadata property, what exactly does this do?

问题描述

我有一个包，它从表中读取数据，并根据列值将记录存储在另外两个表中.包使用事务所需的属性.直到最近我在源表上创建非聚集索引以提高性能时，它才运行顺利.在将进度显示为验证已开始"后，程序包拒绝继续执行.

I have a package which reads data from a table and based on a column value stores the records in two other tables. Package uses Transaction required property. It was running smooth until recently when I created a non-clustered index on source table to improve performance. The package denies to proceed execution after showing the Progress as "Validation has started".

有两件事可以解决问题:

Two things solve the problem:

1. 使用聚集索引而不是非聚集索引
2. 将 ValidateExternalMetadata 属性设置为 False

我不必同时使用它们中的一个，而且包装运行顺畅.我选择了选项 1，但我不明白幕后发生了什么.我的问题是，

I don't have to use both but one out of them and the package runs smooth. I opted for option 1 but I don't understand what is happening under the hood. My questions are,

1. 除了检查列元数据之外，当您将属性设为 false 时会发生什么?
2. 为什么非聚集索引会导致验证问题?

PS:之前表上没有索引.

PS: There were no indexes on the table before.

推荐答案

KB 链接到在上一个答案中应该解决这个特定问题，但没有完全解释 ValidateExternalMetadata 标志的作用.

The KB linked to in the previous answer should resolve this specific problem, but doesn't fully explain what the ValidateExternalMetadata flag does.

与外部系统交互的大多数 SSIS 组件(例如，查找转换或源/目标组件)将定义外部元数据列.这些表示组件正在与之交互的表/视图/查询中的列.此元数据信息缓存在包文件 (.dtsx) 中.

Most SSIS components which interact with an external system (for example, the Lookup Transform, or Source/Destination components) will define External Metadata Columns. These represent the columns in the table/view/query the component is interacting with. This metadata information gets cached within the package file (.dtsx).

在验证阶段，组件应该检查确保包中缓存的元数据仍然与底层表/视图/查询同步.如果不匹配，组件将返回一个特殊状态 (VS_NEEDSNEWMETADATA).在设计时发生这种情况时，SSIS 通过调用 ReinitializeMetadata() 触发元数据刷新.在运行时，这会导致错误.

During the Validation phase, a component is supposed to check to make sure that the cached metadata in the package is still in sync with the underlying table/view/query. If there is a mismatch, the component returns a special status (VS_NEEDSNEWMETADATA). When this happens at design-time, SSIS triggers a metadata refresh by calling ReinitializeMetadata(). At runtime, this results in an error.

因为元数据验证可能很昂贵(例如，具有很多列的大表)，ValidateExternalMetadata 标志可以设置为 false 以禁用此验证.通常，只有当您确定您的包和基础表/视图/查询将保持同步时，您才会这样做.

Because metadata validation can be expensive (large table with a lot of columns, for example), the ValidateExternalMetadata flag can set to false to disable this validation. Typically, you'd only do this when you are certain that your package and underlying table/view/query will remain in sync.

这篇关于ValidateExternalMetadata 属性，这到底是做什么的?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！