Long Validation of SSIS Packages in Visual Studio

Case
Opening my package in BIDS lasts for an eternity because the package validation takes forever. Is there a way to prevent long validation on opening?

Solution
SSIS validates all connections and the related components to make sure the metadata is valid. When you have a slow connection or just a very big package it will take some time to validate (up to several minutes). A big package could be divided in a couple of smaller packages, but the speed of your connections is usually something you can’t change.

Here are a couple of solutions/workarounds:
- Work Offline;
- DelayValidation;
- ValidateExternalMetadata;
- Offline Connection Managers.

Work Offline
There is an option in the SSIS menu called Work Offline. This project level property prevents validation if your connection is unavailable (or very slow). You must set it before opening a package.

Work Offline

There are two concerns/downsides for this solution:
1) You can’t debug/run your package when you are in offline mode (of course, can’t do anything about that).
2) This offline property is only available when you have a SSIS project opened and if you closed the project while there where still packages opened, then it will reopen these packages when the project is loaded in BIDS… That will prevent you to switch to Offline before opening the packages.
You can overcome that by locating the [project].dtproj.user file in the project folder. Open this file in notepad and change the <OfflineMode> property from false to true.

<OfflineMode>

Also see of vote for this change request at Microsoft Connect: Open package in offline mode.

DelayValidation
Setting the DelayValidation property to true will prevent validation at design time. This property is available for the package, containers, connections and tasks.

Delay Validation

There are two concerns/downsides for this solution:
1) Setting it on package level won’t help. Best thing is to set it on all tasks that use the slow connection.
2) On runtime the package will normally validate twice. Always right before executing a task and if DelayValidation is set to false it will also be validated up front. This will catch problems before the package runs for a long time. If your package runs for example 10 hours it would be very disappointing to have a validation error at the end.

So you have to keep that in mind. You could set this property to true while developing the packages and change it back to true before deploying it on the server.

ValidateExternalMetadata
This solution is similar to the DelayValidation property solution. While the DelayValidation property can only be set for the complete data flow task, this property can be set for individual data flow components. Set it to false to gain a shorter package opening time.

ValidateExternalMetadata

There are two concerns/downsides for this solution:
1) If a lot of components in the data flow task use the same slow connection, it could be a bit annoying to set it for all these components. In that case the DelayValidation property is probably faster.
2) Components that have this property on false will not be aware of changes in the metadata of external data sources.


Offline Connection Managers
This is a new option in Denali CTP 3 (SQL Server Integration Services 2012). It will allow you set a single connection to work offline by right clicking the connection manager. See Matt Massons blog post for more info about this new feature.

Offline Connection Managers

There are two concerns/downsides for this solution:
1) You have to open the package before you can set this option. A workaround for that could be to first set the project to work offline before opening the package.
2) If you close the project all connection managers will be reset to work online.


Let me know if you have an other solution/workaround. Also see: Troubleshooting Package Development.