SSIS and Visual Studio 2005 are very slow...

We've been using the Business Intelligence Studio as part of Visual Studio 2005. In general, we find its a well designed ETL tool, but for one problem - its too SLOW!

From what I can see, there are two basic problems: 1) the need to repeatedly validate packages against the database; and 2) the assumption that each package gets its own connection to the database.

Problem #1: Validation

The basic problem with the speed issue seems to be its constant need to validate against the underlying database. For example by default:

I have a project where we're doing replication of tables from a SYBASE database to a SQL Server 2005 database. For each table, we've created a package. We now have 50-60 packages.

Every time you open up the project, it re-validates each package against both the SYBASE database and the SQL database. Each build re-validates. Each execute in Debug mode re-validates. Each run in production re-validates. All this validation on a slow database connection makes basic development tasks very slow.

The same thing happens when running the package in DTExec or through the SQL Agent - it pre-validates every package. This is both good and bad - its good in that it means it will fail your package before trying to execute some bad SQL or badly mapped columns, but given that you're going to run the same package over and over again without a change, there should be a way to turn off this step for packages that haven't changed.

I've looked around and haven't seen any method to change this behaviour - it would be great to have some more lazy validation, e.g. once I have validated it once, don't re validate unless I actually make a change to the package.

Problem #2: Data Sources

In an SSIS Project, there are data sources that you can define once as a common data source. However, there seems to be two problems with the data source model:

1. You cannot store an ADO.NET data source configuration as a shared data source - these have to be re-created in each package. Given how common these of connections are, this seems to me to be a big oversight.

2. Each package when its loaded creates its own connection to the database. Its very slow to create a connection and then tear it down especially with either a slow network or a slow database (e.g. an old legacy connection).

This causes problems for us with so many packages. When I open my project, Visual Studio opens each package and validates it. This causes a connection to created to every database in the package in parallel! With our old SYBASE database, it simply gets overwhelmed when Studio tries to validate 50 packages all in parallel.

My only solution so far is to consolidate packages together - I've been taking 20-30 packages and combining them into a single monster package. This reduces the connections created and speeds up validation. However, this strikes me as unreasonable from a re-usability perspective as it reduces package modularity.

I've done a bit of searching around these settings and I haven't found any way to configure the way that packages are validated - if anyone knows of a way, put a comment up so I can be corrected!