Friday, April 16, 2010

SSIS : OLEDB Destination and Maximum Insert Commit Size

SSIS : OLEDB Destination and Maximum Insert Commit Size

Default is 0 in SSIS 2005.

Default is 2147483647 in SSIS 2008.

Source : SSIS Junkie : Default value for OLE DB Destination FastLoadMaxInsertCommitSize in SQL Server 2008

"Using 0 (2005) or 2147483647 (2008) gives basically the same behavior - all records are committed as a single batch. Depending on exactly what you are doing, you may get better performance by setting MICS to a value between 10,000 and 100,000 - particularly if you have a clustered index on the table you are loading "
from - MSDN SSIS forums

Further reading -
Making Fast Load really fast on clustered indexed tables with SSIS

SQL Server Integration Services (SSIS) - Best Practices

Using Maximum Insert Commit Size with FAST LOAD on the OLE DB Connector, and a Warning

No comments: