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
