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 because the validation process can take a really long time if you have many data sources and/or some of them are to remote servers with a slow network in between.  If you’re in the process of developing a package and you find yourself opening that package often, you can spend an inordinate amount of time waiting for SSIS to validate sources.  It would be nice if SSIS used a background thread to validate, and let you go about your business, but it instead it freezes the UI and does not let you do anything else.

There are three options around this problem: The DelayValidation property, the ValidateExternalMetadata property, and the “Work Offine” option.

DelayValidation property:

Set the DelayValidation property to True to prevent validation at design time to improve performance.  This property is also used 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 property is not available to individual data flow components).  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.

Setting the DelayValidation property to True at the task level can be one of the most time-saving steps you take.

Note that setting the DelayValidation property at the package level does not prevent the initial validation process when you open the package.  It simply delays any package-level validation when you run the package.  That means that the package will still run, but a warning will still appear when you open the package.  This is important if you also want to prevent the validation when the package opens.  In this case, you should set the DelayValidation property to True at the task level.

ValidateExternalMetadata property:

As previously mentioned, 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 (i.e. OLE DB Source component) 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.

By default, the ValidateExternalMetadata property is set to True, so SSIS validates the external metadata whenever you open the package, add a component, or run the package. If you override this behavior by setting the property to False, SSIS will not validate the data source until it runs that component.

“Work Offine” option:

It is possible to turn off validation by selecting ‘Work Offline’ from the SSIS menu.  It still validates what can be validated locally without connecting to databases, such as checking file existence.  Still, since most of the validation time is spent connecting to external data sources, the offline validation is very fast.  Just be aware that you will get validation errors.

Unlike the DelayValidation and ValidateExternalMetadata properties, the Work Offline option is available even before you open a package.

When you are ready to run the package you will need to turn off “Work Offline”.  And fortunately it won’t try to validate all the connections when you do turn if off.  It will only validate a task when it is about to execute the task or if it’s a data flow task and you open it.

Be aware that if you have “Work Offline” selected and open a data flow task, it still won’t try to validate the connections.  And tasks that are disabled will still be validated if “Work Offline” is turned off.

Notes:

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 three options described in this section.

It’s a good idea to close all tabs before exiting Visual Studio, as it will remember what packages you had open and then open those packages again when you open the project next time.  So if you don’t have “Work Offline” selected, and have tabs open with a lot of connections, the next time you open that project all those connections will be validated and you will be stuck waiting for it to finish.

More info:

Open SSIS packages without validation using these SQL properties

Troubleshooting Design-time Validation Issues