When to use the fast-load option for data access mode in OLE DB destination?

Generally the fast load option is the preferred method from the performance stand point. But this option commits rows in batches. If you want to capture only the offending rows when an error occurs in an OLE DB destination, then you should not use this option. 

By default, any constraint failure at the destination causes the entire batch of rows defined by FastLoadMaxInsertCommitSize to fail. The FastLoadMaxInsertCommitSize property of the OLE DB Destination is used to determine how many rows should be committed as a single transaction when using the FastLoad option. In SQL Server Integration Services (SSIS) 2005 the default value for this property was zero which meant “regardless of the number of rows, commit all of them under a single transaction”. In SSIS 2008 that default value changed to 2147483647 (see below), meaning that SSIS would commit rows after approximately 2.15 billion.




Example:

I have a constraint on the dbo.Sales_Summary table to restrict the ProductLine to R, M, or T only. But some rows have 'S'. When I use the Fast Option, all of the 2894 rows in the batches are re-directed to the error destination, even for those with the valid productline code 'M', 'R' or 'T'.



For the same package, if I do not use the Fast Load option (i.e., use the "Table or view" option), the 421 invalid rows with 'S' as the productline are routed to the error destination, whereas the valid ones are inserted into the destination table