Another recycled post from days gone by...
One of my favorite blogs on SQL Server Integration Services (SSIS) is by Jamie Thompson. He does a tremendous job sharing tips, tricks, and lessons learned from working with SSIS. Recently he posted a blog on three of the destination adapters and the performance implications of using each (http://blogs.conchango.com/jamiethomson/archive/2006/08/14/4344.aspx). The blog was pretty timely, as I had been having this exact conversation with a client the previous week. I wanted to add a couple of additional thoughts on the material.
First, after reading the blog, one would ask oneself "Self, why would I ever use the OLE-DB Destination without FastLoad?" Well, that is a good question. Undoubtedly, this method is extremely slow. Reiterate, extremely slow. OK, slow compared to the counterparts "with FastLoad" or SQL Server Destination. If you run Profiler while loading into each type of destination, you will quickly see that the OLE-DB Destination without FastLoad inserts each row individually. Obviously, this is very slow.
OLE-DB Destinations with FastLoad cannot be set to redirect error rows. Consider a situation where you would want to trap error records in the destination - such as if a record violates a primary key constraint, or a domain constraint. If you want to redirect these rows and allow the load to succeed while trapping those bad records for assessment and possible reload at a later time, you CANNOT use FastLoad. This only leaves OLE-DB without FastLoad, since the SQL Server Destination does not support this behavior either.
So you may then ask yourself “Self, why would I choose to use the OLE-DB Destination with FastLoad over the SQL Server Destination?” Another good question. Well, to use the SQL Server Destination, the destination database must be on the same server on which you are executing the SQL Server Integration Services package. From my experience, you will not see a significant performance difference between the two solutions (this is subjective and you should test with your own loads!). I generally prefer the OLE-DB Destination simply because it gives you better flexibility on where you execute the package. You may make a decision that the package will always execute from the server where the destiniation resides - but you are painting yourself in a corner and limiting your ability to easily scale out in the future.