DelayValidation property and ValidateExternalMetadata property in SSIS

When we are developing packages at times it happen that we cannot connect to our data sources or portions of package cannot validate until prior task in the package have excuted at run time.


When you are developing packages in BI studio the data sources are not available at development time or you have using one table that would create by previuos Execute SQL task. As the table will create at run time it will give validation error at development time.

SSIS includes the following features to help you aviod validation errors that would otherwise resulted from the below conditions.

Work Offline when data sources are not available:

Normally, SSIS Designer tries to connect to each data source that is used by your package to validate the metadata associated with sources and destinations. This causes validation errors when the data sources are not available. To prevent these connection attempts, you can enable Work Offline from the SSIS menu. Unlike the DelayValidation property, the Work Offline option is available even before you open a package. You can also enable Work Offline to speed up operations in the designer, and disable it only when you want your package to be validated.

Configure the DelayValidation property:

on package elements that are not valid until run time. You can set DelayValidation to True on package elements whose configuration is not valid at design time to prevent validation errors.

For example, you may have a Data Flow task that uses a destination table that does not exist until an Execute SQL task creates the table at run time.

The DelayValidation property can be enabled at the package level, or at the level of the individual tasks and containers that the package includes.

BY Default it will be False. We need to set it true.

Normally you must leave this property set to True on the same package elements when you deploy the package, to prevent the same validation errors at run time.

The DelayValidation property can be set on a Data Flow task, but not on individual data flow components.

You can achieve a similar effect by setting the ValidateExternalMetadata property of individual data flow components to false.

However, when the value of this property is false, the component is not aware of changes to the metadata of external data sources


Thanks,