Bulk Insert option for SSIS ADO.NET Destination which is available since SQL Server 2008 R2 improves data load speeds significantly. This option is enabled on the ADO.NET Destination component by selecting the “Use Bulk Insert when possible” check-box (Screen capture 1)

Screen Capture 1 – ADO.NET Bulk Insert

Using the bulk insert mode does come with a catch, especially when the destination table has Foreign Keys. You would notice after the data load, the WITH CHECK constraint on Foreign Key becomes WITH NOCHECK. Probably this behaviour is because of the ADO.NET Destination component’s implementation of SqlBulkCopy which ignores check constraints by default.  The net effect  is that the  ETL would fail to catch data integrity issues which might result in cube processing failures at downstream.

Suggested Workarounds:

1. Many ETL frameworks disable foreign keys before data loading to take advantage of parallel data loading and enable foreign keys just before cube processing. Such ETL frameworks would be immune to the above behaviour as the data integrity exceptions are caught before the cube gets processed.

2. Another alternative would be to use OLEDB Destination with fast load option which has comparable data load speeds as ADO.NET Destination in Bulk Insert mode.

Benny Austin

@BennyAustin Specifically, it uses the default settings of SqlBulkCopyOptions msdn.microsoft.com/en-us/library/… #SSIS

—
Matt Masson (@mattmasson) October 03, 2012