I believe it can be very useful to share success stories and case studies, particularly when a seemingly insurmountable problem has been overcome so I'm going to enjoy providing you with a SSIS success story right now.
A guy going by the name Andy (he didn't give his surname) was having trouble using SSIS to pump the contents of a 5.6m rows table from Sybase to SQL Server and was asking me about it in the comments section of my blog entry Destination Adapter Comparison. In his own words he was observing that "The first 800k comes over in the first minute. It slowly descreases over time. After 10 minutes and 3 million rows its down below 200k/minute." Even more embarrassingly for SSIS was that he built a DTS package to do the same job and that "finishes all 5.6 million rows in ~4.5 minutes".
I would like to be able to say that I was able to provide a lot of help to him but sadly that wasn't the case, I wasn't able to provide much advice other than "try and work out where the bottleneck is". Andy was eventually able to determine that the problem was in the OLE DB source adapter and eventually he posted the following:
"Well I'll be a monkey's uncle. Lowering the DefaultBufferMaxRows to 1000 performed the magic I was looking for. It has performed the transfer in 3 minutes twice now."
Excellent news. The lesson to be learnt here is not that changing the DefaultBufferMaxRows property will speed up a dataflow, its that SSIS has a lot of knobs that you can tweak to eek out more performance from the pipeline. In this case Andy was able to get the total execution time down from 26 minutes to 3 minutes which is a stupendous improvement just from changing DefaultBufferMaxRows. Its also gratifying to see that the SSIS pipeline can blow the socks off DTS in a straightforward select-and-insert.
If you want to know more about performance tuning the SSIS dataflow then your first port of call should always always be a whitepaper called "Integration Services: Performance Tuning Techniques" by Elizabeth Vitt and Hitachi Corporation which you can read here: http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx.
Note that this isn't an invite to blast my blog with comments asking for help in the vain hope that I might be able to help :) The correct place to ask for assistance, as always, is the MSDN SSIS forum.
-Jamie