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 meaning that SSIS would commit rows after approximately 2.15 billion and I was puzzled as to why so I asked the question on the SSIS forum on MSDN. Bob Bojanic, a developer on the SSIS team, replied with the following:
In SQL Server 2008, SQLServer Engine is acquiring a different set of locks than it used to do for SQL Server 2005. This change on the lock is creating a conflict in the SSIS pipeline causing an application deadlock. This was happening when packages used bulk-insert and update into the same table at the same time.
We changed the default value of this property to avoid the mentioned deadlock. There is also the upgrade code which changes the value of this property when SQL Server 2005 packages are upgraded.
So, now you know! Setting FastLoadMaxInsertCommitSize=2147483647 won’t affect you unless you’re inserting more than that many rows; if you ARE inserting more than 2147784647 rows then let me know because I’d love to hear about it :)