In SQL Server Integration Services, when a package is opened in BIDS or components are added to it, SSIS by default validates all connections/data sources used by various components of the package. The validation process ensures that the external metadata is valid. If the metadata is not valid, user will receive warning or error messages pointing to the problem. So this process helps to find and fix bugs at design time itself. However there is a cost linked with it-the amount of time it takes to validate each connection and that is quiet significant in case of a large SSIS package using data sources in plenty.
Hence at times, there is a need to override the default behaviour of validation and open the SSIS package without validation as it can take a really long time in case of multiple DS and if some of them have to connect to remote servers with a slow network in between. This would let user to wait for an unwarranted amount of time every time he opens the package and wants to work on it. This is even worse in case package is situated at a remote location and user is accessing it from his/her machine. In that case, even a minor glitch in network will force SSIS to validate package again and that contributes to tome again.
It would have been of great use if SSIS used a background thread to validate, and let user go ahead with the package, but it instead it freezes the interface and does not let you do anything.
There could be many ways to troubleshoot this problem like
- Removing the unused data sources from SSIS package,
- Optimizing data connections,
- Using Delay Validation property or
- Using ValidateExternalMetadata property
However, best of all is using “Work Offline” option which reduces the validation time up to a great extent and user does not have to wait indefinitely before he can start working on package.
Validation can be turned off by selecting ‘Work Offline’ from the SSIS menu. Although SSIS still validates what can be validated locally without connecting to external databases, such as checking file existence. We need to note that since most of the validation time is spent connecting to external data sources, the offline validation is very-very fast and completes within seconds.
The added advantage of using Work offline option over the DelayValidation and ValidateExternalMetadata properties is that the Work Offline option is available even before user opens up a package. Thus it will even save the first time validation time spent on SSIS package.
Before executing the SSIS package, you will need to turn off “Work Offline”. The good thing is that it still won’t try to validate all the connections when you do turn it 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.
Please refer to the following steps to set the SSIS work in Offline Mode:
Step 1# Open the solution file (.sln).
Step 2# If the package is loaded along with the solution, SSIS will start validating it without giving you the option of setting it in Offline mode. There are 2 ways to get rid of it:
a) Let the package load, open and validate for the first time solution is opened. After validation is completed, close the package (.dtsx file) in the designer, click on save and close the solution. Next time the solution is opened, package will not load automatically and user will have the option to turn the SSIS in offline mode. (To set the SSIS in offline mode, refer to the 3rd point below)
b) Before opening the solution file, look for .suo file in the solution folder. Normally this file validation is completed, close the package (.dtsx file) in the designer, click on save and close the solution. Next time the solution is opened, package will not load automatically and user will have the option to turn the SSIS in offline mode. (To set the SSIS in offline mode, refer to the 3rd point below) is hidden so you need to change the folder settings to view this file. It contains user specific information about Visual Studio environment regarding the solution.
Delete it and Visual Studio will not automatically open any package or file when the solution is opened. Now you can set the SSIS in offline mode.
Step 3# After the solution is opened, look for SSIS menu at the top and select (tick) the “Work Offline” option as shown in pic below:
Step 4# Now open and load the desired SSIS package from the Solution Explorer tab. Please be noted that the validation is still performed, however that is very basic and local and thus it is really fast.
Step 5#Just before executing the package it is required to turn off the Offline mode and this can be done easily by going back to SSIS menu and ticking off the Work Offline option as shown in pic below :
Advantages of using Offline Mode
1. Working in offline mode makes validation process very-2 fast as only basic validation is done. This usually converts validation time from several minutes to few seconds and thus saves a lot of indefinite waiting time.
2. The advantage is multi fold in case used needs to open the package multiple times again and again purposely or not(in case of connection issues, validation issues or passing over to the people working across different geographical locations)
3. The benefit over using other features like Delay Validation and ValidateExternalMetadata is that the Work Offline option is available even before you open a package. Thus first time validation effort is also saved.
4. The package can be turned on back to online mode very easily by ticking off Work Offline option.