ETL Best Practices (2) – “Work Offline” in development
When developing SSIS packages in BIDS, you would want to turn on the “Work Offline” option. This will allow BIDS to load packages without validating every connection or the schema for every query.
Step 1: Open your SSIS project from BIDS, but do not open any package yet.
Step 2: From the SSIS menu, turn on “Work Offline”
Step 3: Once you have the package opened in BIDS, you need to turn off the “Work Offline” option in order to create/modify your queries against the sources.
Potential problems with validation in development:
1) When you have a large volume of data, opening your package from BIDS can take a long time.
2) If your login credential has expired, the connection will fail. Worse yet, the multiple login failures from BIDS will count towards your allowable tries, and your source server might lock up your account.
Another way to stop the validation during development, is to set the delay validation to True for every connection and component. But remember this is a runtime setting that will just delay the validation of a component until just before it executes.