Maximum insert commit size and Rows Per Batch in OLE DB Destination in SSIS

The Maximum insert commit size (MICS) controls how much data is committed in its batches.

So, if you have MICS set to 5000 and you have 9000 rows and you encounter an error in the first 5000 results, the entire batch of 5000 will be rolled back. MISC equates to the BATCHSIZE argument in the BULK INSERT transact-sql command.

Rows Per Batch (RPB) is merely a hint to the query optimizer. The value of this should be set to the actual expected number of rows. RPB equates to the ROWS_PER_BATCH argument to the BULK INSERT transact-sql command.




Specifying a value for the MICS will have a few effects. Each batch is copied to the transaction log, which will cause it to grow quickly, but offers the ability to back up that transaction log after each batch. Also, having a large batch will negatively affect memory if you have indexes on the target table, and if you are not using table locking, you might have more blocking going on.[@more@]