Microsoft OLE DB Provider for DB2 in SSIS vs. ODBC in DTS

I finally made data transfer between AS400 and SQL2005 work by using Microsoft OLE DB provider for DB2. I have tried other methods, some do not have build-in destination, some have unicode conversion problem. The only issue I have with this method is the peformance. For example, 300,000 rows load from AS400 take only 3 minutes with the ODBC connection in DTS, but more than 5 minutes with the microsoft OLD DB provider for DB2 in SSIS.

Does anyone have the same issue or figured out any tweaking that may speed up the transfer?

Thanks.

In the 5 minute example, what was your destination?|||The destination is the same - an OLE DB connection that connect to a SQL 2005 server.|||

Jane2006 wrote:

The destination is the same - an OLE DB connection that connect to a SQL 2005 server.

And are you using the "fast load" option?|||

Since I also need to load data back from SQL2005 to AS400, I could not use ADO.NET/ODBC method unless as someone else suggested, build a component for that missing destination microsoft does not have time to build. Also, the unicode issue drives me crazy. So I decide to use Microsoft OLE DB Provider for DB2.

Based on my experience, only the version released in April 2007 is good on 64-bit machine. The old versions did not work on our 64-bit machine even thought it said it should.

|||

Are you talking about the Data access mode in OLE DB Destination Editor? Yes. I am using the fast load. Actually this is a default unless changed.

|||

Jane2006 wrote:

Since I also need to load data back from SQL2005 to AS400, I could not use ADO.NET/ODBC method unless as someone else suggested, build a component for that missing destination microsoft does not have time to build. Also, the unicode issue drives me crazy. So I decide to use Microsoft OLE DB Provider for DB2.

Based on my experience, only the version released in April 2007 is good on 64-bit machine. The old versions did not work on our 64-bit machine even thought it said it should.

This is irrelevant. On the OLE DB destination, are you using the "Table or view - fast load" option when loading your results from AS400 to SQL Server?|||Yes. I think there must be some settings in Microsoft Provider OLE DB Provider for DB2 that needs to be tuned up. I just don't know where.|||How many rows are you retrieving and what is your MaxInsertCommitSize set to?|||Retrieving 3048740 rows from AS400. Maximum insert commit size is 0.|||Try setting MaxInsertCommitSize to 20000 and see what happens.|||Just give it a try. Not much difference. Still more than 5 minutes.|||Any transformations? Or just source -> destination?

Are you specifying a query in the source connection, or selecting a table from the drop down list?|||The one fundamental difference here is that you are using a Microsoft DB2 driver. It isn't a native AS400 driver. It's a 3rd party driver as far as connecting to the mainframe is concerned. You can download the IBM DB2 driver and you might have luck with that.

This isn't the same as using ODBC, which would (perhaps) use a native driver to connect.|||

No transformation. A straight source to destination. I am using SQL commnd for this particular one.

I have used tables in other scenarions as well. The row sizes are much smaller than this one, but ODBC consistently has better performance, even though only several seconds better.