Short answer:

No.

Long answer:

Noooooooooooooooooooooooooooooooooooooooooooooooooo.

UPDATE:

I was told that some people would like an explanation instead of blindly trusting my advice (weird), so here goes:

In SQL 2005 RTM, the SQL Server Destination was the fastest way to load data into SQL Server with SSIS. Somewhere along the way (in the 2005 SP2 / 2008 timeframe), the performance of the OLE DB Destination (using Fast Load) surpassed the SQL Server Destination in many scenarios. This was due to a couple of different reasons:

• Multiple improvements were made to the SQL Native Client OLE DB provider
• Additional checks and constraints were added to the shared memory based loading mechanism used by the SQL Server Destination

I saw results from some performance tests we ran shortly after the 2008 release and was surprised to see that the SQL Server Destination could be 10% faster to 10% slower than loading the same data with the OLE DB Destination. The performance would vary based on the system the tests were run on, the data types involved, and the overall row width. While it does appear that the SQL Server Destination can be faster, it’s just as likely that it will be slower. In most cases, the performance is about the same.

My recommendation is that if you need every bit of performance (a 10% perf increase on a 10 hour load can be significant), try out the SQL Server Destination to see how it works for you. However – keep in mind the following limitations of the SQL Server Destination:

• You must have SSIS running on the same machine as the destination database
• You must run the package as an administrator
• It is very difficult to debug when things go wrong

Given these limitations, I recommend using the OLE DB Destination even if you are seeing a performance increase with the SQL Server Destination.