Do people still recommend NOT using the SQL Server Destination? I know there have been questions about this in the past, but they seem to be from 4+ years ago. I have been timing OLEDB versus SQL Server Destination for ~5million+ records, the time savings are definitely enough where I need to consider it. The source/target databases and ssis packages are located on the same server.
Asked
Active
Viewed 631 times
1 Answers
4
The source/target databases and ssis packages are located on the same server.
Since you have above, you can use SQL Server destination. From Data loading performance guide :
The SQL Server destination is the fastest way to bulk load data from an Integration Services data flow to SQL Server. This destination supports all the bulk load options of SQL Server – except ROWS_PER_BATCH.
Be aware that this destination requires shared memory connections to SQL Server. This means that it can only be used when Integration Services is running on the same physical computer as SQL Server.
Internally, SQL Server destination uses Bulk Insert .. uses Shared Memory which is extremely fast (you can confirm running a trace to see what it is executing behind the hood).
