SSIS - Speeding Up Package Load Time

Your Package Will Load...Someday 

While working with SSIS, I was often frustrated by how long the package takes to load for editing. Frequently, I was simply interested in opening the SSIS packages to see what they did, but really had no need to actually edit them. Sadly, if you try to open an SSIS package on a machine that either does not contain all of the databases referenced in the package or that cannot connect to those databases, you often have to wait an incredible length of time for the package to open before you even have a chance to change the connections to something valid.

I discovered a small trick which cures that dilemma - open the SSIS package in a text editor (the package content is actually XML), make some minor edits, save, then finally open the SSIS package in BIDS.

Delaying Package Validation Until Later

 Open your package in a text editor and search for all instances of

 "DelayValidation">0</DTS:  

Replace those with

"DelayValidation">1</DTS:

This simple change will prevent BIDS from attempting to connect to and validate every object before allowing you to view the content of the SSIS package. It will still attempt to validate an object when you click on that object to edit it, but at least you won't have to wait for it to time out on its attempts to validate every item.

Correct Connection Strings

Open your package in a text editor and search for all instances of

<DTS:Property DTS:Name="ConnectionString">

Check the value for Data Source and Initial Catalog - are these correct for the environment you're working in? Many times, the package may have been created with connection strings specific to the server they were built on and either the server no longer exists or is no longer accessible. By correcting the connection string information prior to opening the package in BIDS, you can speed up the package load time and avoid the flood of validation errors that go along with incorrect connection information.*

=======
* Yes, I know there are some SSIS tricks for handling different connection strings, such as using a configuration file to hold the connection information, however many SSIS packages that people inherit do not have configuration files for the connection information.