SSIS Component的ValidateExternalMetadata属性
来源:互联网 发布:汶川地震知乎万人坑 编辑:程序博客网 时间:2024/05/18 19:45
ValidateExternalMetadata Property
Indicates whether the component validates its column metadata against its external data source at design time. When this property is true, the component connects to its external data source during design time and validates the columns in its input or output collections against the columns at the external data source. When set to false, the component does not perform this "connected" validation.
ValidateExternalMetadata Property是Componet的属性,在VS中,出现在Data Flow选项卡中的组件存在该属性。
默认情况下,ValidateExternalMetadata属性是设置为True的,因此任何时候我们打开包、添加组件或者运行包,SSIS都会链接到数据源,并验证外部元数据。当ValidateExternalMetadata属性是设置为False时,SSIS在运行到该组件时才验证数据源,在Desgin time不会验证源数据。
在Design time时,发现SSIS 组件对外部源数据貌似有种缓存机制,不会实时更新metadata。
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).
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.
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.
适用的case是,一个Staging表在Design time是不存在的,当package runtime时,该组件的上流组件创建Staging表,当package运行到该组件时,由于Staging表已经存在,该组件验证通过,能够运行成功。
如果开启DTC Transaction,ValidateExternalMetadata Property设置不当会导致死锁,亲测,确实会导致死锁,在debug时,死锁不会自动解除。详细情况请参考:https://support.microsoft.com/en-us/kb/2253391
The execution of a SQL Server Integration Services (SSIS) package stops responding when you enable DTC transactions for a package in Microsoft SQL Server
SYMPTOMS
- You create a SQL Server Integration Services (SSIS) package in Microsoft SQL Server 2005, in SQL Server 2008, or in SQL Server 2008 R2.
- The SSIS package contains a data flow task and some other tasks.
- The TransactionOption property of the SSIS package is set to Required to use DTC transactions.
- The other tasks run in a DTC transaction before the execution of the data flow task.
- You add a component to the data flow task.
- The ValidateExternalMetadata property of a data flow component is set to True.
- The data flow task contains an OLE DB Destination component that has the data access mode set to Table or view, or to Table name or view name variable.
CAUSE
RESOLUTION
- Set ValidateExternalMetadata for all component in the data flow task to False
- Set the data access mode of the OLE DB Destination component to one of the following modes:
- Table or view – fast load
- Table name or view name variable – fast load
- SQL command
MORE INFORMATION
For more information about how to use SQL Server Profiler, visit the following Microsoft Developer Network (MSDN) website: Using SQL Server Profiler
For more information about how to troubleshoot SSIS packages, visit the following Microsoft Developer Network (MSDN) website: Troubleshooting Package Development
- SSIS Component的ValidateExternalMetadata属性
- Intent的属性-Component属性
- SSIS - NoMatchBehavior Error fail Component
- Ext.Component的ref属性
- Ext.Component的ref属性
- Component属性
- SSIS之操作Script Component组件的Blob Column类型的处理
- 使用正确的姿势更新SSIS Data Flow Component以适应字段类型变化
- SSIS常用的包—通用的属性
- SSIS: Throwing errors from script task/component
- 利用SSIS的ForcedExecutionResult 属性 和CheckPoint调试Package
- 5 Component属性
- Hibernate组件属性-component
- SSIS - Script Component, Split single row to multiple rows
- 通过Intent的Component属性来启动另一个Activity
- context:component-scan标签的use-default-filters属性
- 什么是SSIS的Checkpoint
- SSIS的一个问题
- JXL 操作Excel
- 读书笔记 effective c++ Item 13 用对象来管理资源
- [LInux]VScode的安装和调试
- 使用TP5数据库时的一些小积累
- javascript面试题
- SSIS Component的ValidateExternalMetadata属性
- 快速入门互联网协议
- HashMap源码探究
- 过渡动画的使用
- java复习思维导图
- Java常见设计模式
- 如何实现导航菜单栏中的二级下拉菜单?
- 设置centos7中的mysql5.7不区分表名大小写有关操作
- 单点登录原理与简单实现