Very nice. I'm guessing the Microsoft Ole Db Provider for DB2 is different from the IBM Ole Db Provider for DB2 that we are using currently?

Sure, and have better performance in SSIS than IBM one.Vctor M. Snchez Garca (ES) (BI) Hope this help. Please vote if you find this posting was helpful. if this is an answer to your question, please mark it. http://bifase.blogspot.com | http://twitter.com/atharky

Yeah, when we used the ADO.net destination, we changed the row commit size to I think 10,000. But when we observed the number of rows increase as we put the data in, it was still incrementing at a lower rate than that. Where can I find some more information about Microsoft Connector for DB2? After looking on Google, I couldn't find much besides Microsoft Connector for Oracle/Teradata. Is Microsofot Connector the Ole DB Provider for DB2? Because that is what we are using currently for the Ole DB Command. For the ADO.net destination, we used native ODBC drivers for DB2.

Those connectors are provided as part of "feature packs" for SQL Server. Check here for the feature pack relevant to your version of SQL Server. Talk to me now on

The overall function that we are doing is trying to parse a large text file and inserting the parsed data into DB2. We have jobs that are writing using "ole db command" (I know this is inefficient and would be much faster with CozyRoc), but when running the jobs using ODBC to write 1,000,000+ rows to DB2, it seems like the SSIS job opens and closes connections, using a new one each time, instead of re-using connections. Is there some setting to get around this? Also is there a way to ensure the commits are happening every 10,000 rows as opposed to 10 rows (bulk inserts)? We tried to use the ADO.NET destination to try to do this, but it seems like it has the same functionality as the Ole DB Command where it just inserts every few rows into the database. Is there anyway to force it to put a set particular number of rows at once? Thanks!

If I remember, there are also some setting in DB2 that may affect the row commit size. Also, have a look at the Microsoft Connector for DB2 (available if you run Enterprise Edition). That may have better performance than both ODBC and ADO.NetTodd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

John, If you need assistance with the Microsoft OLE DB Provider for DB2, please post any questions to our forum: http://social.technet.microsoft.com/Forums/en-US/biztalkhis, as we don't really monitor the SQL forums. Thanks - CharlesCharles Ezzell - MSFT