ValidateExternalMetadata property, DelayValidation property SSIS , Package Validation in SSIS , Long time to load packages due to validation in BIDS

When you open a SQL Server Integration Services (SSIS) package in SSIS Designer or add components to a package, SSIS by default checks the data sources used by the various components. This process for SSIS package validation ensures that the external metadata is valid. If the metadata is not valid, you'll receive warning or error messages pointing to the problem. At times, you might want to override the default behavior and open the SSIS package without validation.

• To prevent these connection attempts, you can also 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.


• If you have multiple SSIS projects in a single solution, there is a way to enable the Work Offline option prior to actually opening the project. Locate the BIDS *.user file associated with the project. For SSIS, this file should be located in the same folder as the project (.dtproj) file, and will have a filename like “[project].dtproj.user”. Open this file in Notepad, locate the [offlinemode] tag and change the value from false to true. Now, when the project is opened, it will already be in Offline mode, so you won’t have to suffer through a lengthy validation process.


• 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.


• 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.


• If database objects that are used by the package are locked when validation occurs, the validation process might stop responding. In these circumstances, the SSIS Designer also stops responding. You can resume validation by using Management Studio to close the associated session in SQL Server. You can also avoid this issue by using the settings described in this section.

Reference: MSDN BOL