Hi there, I'm Ash. I a member of the DTS SSIS team here at Microsoft.
Here're some tidbits on improving data flow performance. Some of these I've picked up recently in working with a customer (thanks Evan). As with any perf guidance, your mileage may vary.
- remove any unnecessary columns not used in the data flow by removing them from the query in the source adapters. Data flow engine warns of these, so they're easy to isolate. This saves the engine from unnecessarily copying and allocating for data that's not needed.
- default lookup query is 'select * from <referencetable>'. This is done because during the design time, the designer gives you all available columns to pick and choose for joins and passthrough. Once you've identified which columns are required, modify the query to select only the columns that are really necessary. Sure beats getting customer's address/city/state/country/zip/age/gender/phone/etc. when all you need is to look up a key and get the surrogate key out.
- optimize the heck out of the source adapter SQL query using traditional query optimization tools and techniques. SSIS doesn't optimize the query on your behalf.
- if loading into SQL Server, check out the Books Online guidance on how to do inserts with minimal logging. Another BOL article talks about when to drop indices and when to leave them in place. If using Yukon, consider using Partitions.
- If running on the same machine as SQL Server, use the SQL Server Destination component instead of OLEDB Destination.
- As a general guidance, a Source adapter could get a thread, a Destination adapter could get another, and any asynchronous transforms could get their own.
- If using the Slowly changing dimension transform, consider using a Lookup right before the SCD to quickly identify rows that don't exist in the dimension table. Due to it's design+implementation, SCD needs to be more dynamic whereas Lookup can be used to create an in memory cache for quick operations.
More as I run across them. If you're using SSIS already, I'd love to hear what you're up to.