ValidateExternalMetadata property, what exactly 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. Use a clustered index instead of non-clustered
2. Set ValidateExternalMetadata property to False

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. What exactly are the things that happen when you turn the property false, apart from checking column meta data?
2. Why is non-clustered index causing a validation trouble?

PS: There were no indexes on the table before.

回答1:

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

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.

回答2:

This may be the explanation:

http://support.microsoft.com/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