Ever wonder why your SSIS packages are taking forever to load? This is because SSDT is trying to validate your packages. What happens is SSIS checks the data sources in the package(s) that are open and checks to ensure that the external metadata is valid. If the metadata is not valid you will get error/warning messages.

You can spend quite a lot of time waiting for SSIS to validate your packages. This can be due to the fact that you have many data sources and components in your package, or slow network connectivity.

When you are in the process of developing you might want to delay the validation. There are three different methods to delay validation, Delay Validation property, Validate External Metadata property and the “Work Offline” option.

Delay Validation:
You can set the DelayValidation property to true at a package level and at a task level.

Package Level
packagedelay
Task Level
taskdelay
Note that setting the DelayValidation property to true at package level does not prevent the initial validation when you open the package, it simply delays the validation until you execute the package.

If you want to delay the validation when the package opens, you will want to set the DelayValidation property to true at task level.

Validate External Metadata:
The DelayValidation property can be set at task level but not on individual components within the task, this is where the ValidateExternalMetadata property comes in. You can set the ValidateExternalMetadata property to false on a Data Source. By doing this the component will not be aware of changes to the metadata or external data sources. By overriding the default value and setting the property to false, SSIS will not validate the data source until the component is run.
externalmetadata

Work Offline:
You can turn off validation by selecting the “Work Offline” option on a connection.
workoffline
It validates what can be validated locally without connecting to a database, such as flat file connections where it will check if the file exists.

Most of the validation time is spent connecting to external data sources, therefore the “work offline” option is very fast. Be aware that you will get validation errors while you are working offline.

Tip:
SSDT remembers all the packages you had open the last time you closed the application. Therefore, when you open your project it will validate every connection in all those packages and you could be stuck for a long time waiting for SSDT to respond. It is recommended that you close all open packages before closing SSDT.

d7ec408f831610926b7047e7e551f86d

Leave a Reply

Your email address will not be published. Required fields are marked *