The SQL 2005 SSIS OLE DB Destination has the propety “Maximum insert commit size” for setting the batch size of the underlying bulk insert statement. It seems to me it is not working as described. When the value is zero, the bulk insert finished in one single transaction. When the value is nonzero, the batch size seems to be the limitedby buffer size of the SSIS data flow.
For example, the Maximum insert commit size is set to 3000000. But, the data flow buffer can only has 70000. Then, the BATCHSIZE is set to 70000 for the bulk insert.
I found out from a web site there are three factors to affect the transaction commit. It seems to be the buffer size.
The OLE DB Destination will commit (This is important)
When it has processed #FastLoadMaxInsertCommitSize rows
When it has reached the end of the buffer
At the end of the data flow if the value of FastLoadMaxInsertCommitSize is 0
In 2005, the batch size 0 means one transaction. In 2008, the batch size 2,147,483,647 means one transaction.
If it is multiple transaction, the number of rows are hints. The actual number of row is affected by the data flow buffer size, the batch size, and the commit size.
See the section “A Word About BATCHSIZE and ROWS_PER_BATCH” from “The Data Loading Performance Guide” for more information.