none
SSIS- Advantage to set ValidateExternalMetadata to False in OLE DB Source and Destination RRS feed

  • Question

  • What are the Advantages to set ValidateExternalMetadata to False in OLE DB Source and OLE DB Destination.

    In terms of performance while executing SSIS job on sql server?

    I am reading millions of records so could't able to figure it out.


    • Edited by Iva2290 Tuesday, December 5, 2017 8:45 AM
    Tuesday, December 5, 2017 8:36 AM

Answers

  • Hi lva2290,

    Someone explained the property:

    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.

    Reference: ValidateExternalMetadata property, what exactly does this do?

    Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Visakh16MVP Tuesday, December 5, 2017 9:18 AM
    • Marked as answer by Iva2290 Wednesday, December 6, 2017 6:05 AM
    Tuesday, December 5, 2017 8:44 AM
    Moderator