Difference between BULK INSERT and SQL Server Destination in SSIS

The Bulk Insert task uses the Transact-SQL BULK INSERT statement for speed when loading large amounts of data. The SQL Server destination uses logic similar to but even more optimized than the logic used by the SQL Server built-in BULK INSERT command. Because the SQL Server destination uses shared memory for maximum loading speed, the package containing the SQL Server destination must execute on the same server as the database engine. Both the Bulk Insert task and the SQL Server destination can specify options that would be specified in the Transact-SQL BULK INSERT statement. Using the SQL Server destination is faster than either the Bulk Insert task or using the OLE DB destination with one of the fast load options. The fast load option of the OLE DB destination works by using optional parameters of SQL Server's BCP (bulk copy) utility; these optional parameters include:

· Keep identity values from the imported data file or use unique values assigned by SQL Server.

· Retain a null value during the bulk load operation.

· Check constraints on the target table or view during the bulk import operation.

· Acquire a table-level lock for the duration of the bulk load operation.

· Specify the number of rows in the batch and the commit size. The number of rows in a batch defaults to 100.