A few days ago, I was working with a client that recived the following warning on the connection managers in his SSIS packages.
[OLE DB Destination ] Information: The Maximum insert
commit size property of the OLE DB destination "component
"OLE DB Destination" (40)" is set to 0. This property setting can cause the running
package to stop responding. For more information, see the F1
Help topic for OLE DB
The packages were executing successfully, but he wanted to prevent that warning message. This was an easy fix.
When you are using an OLE DB destination, and you choose the 'Table or View - fast load' as your data access mode, you will see the 'Maximum Insert Commit Size' property. This is where you can specify the maximum number of rows that can be processed and inserted into the destination in one batch insert. By default, this property is set to 2147483647. There are two ways to change this value.
You can right click on the destination in the data flow task and click edit. You will see it on the Connection Manager tab.
The other way to change it it in the properties pane for the destination. You can find it under the custom properties section.
I don't know how the default value was changed to 0, but it may have had something to do with the fact that he was converting these packages from DTS to SSIS.
Were these DTS packages being converted to SSIS 2005 or 2008? Because I believe in SSIS 2005 the default value is 0, but in 2008 the default value is 2147483647. http://www.bidn.com/blogs/DustinRyan/ssis/136/ssis-2005-package-hangs-at-ole-db-destination
Thanks Dustin. That's good to know. I think he may have been trying to convert to 2005.