(This has also been posted on MSDN Forums so apologies for the replication)
I am trying to get the best performance driver for transfer of data from AS400 to SQL Server 2005 using SSIS. I am comparing it to DTS packages using Client Access ODBC Driver (32-bit) which I'll call std ODBC
.Net Provider/ODBC works but is VERY slow. I understand that Microsoft has simply put the .Net layer on top of the std ODBC driver and this overhead is slowing it right down (to a crawl in my opinion)
.Net Oledb/ IBM DB2 for iSeries IBMDA400 works and is about 2.5 times faster than .Net/ODBC, but still about 3x slower than std ODBC
.Net Oledb/ IBM DB2 for iSeries IBMDASQL is similar.
Microsoft OLE DB Provider for DB2 is supposed to be the fastest but it requires upgrading to Enterprise Edition, which is not a trivial cost.
Can anyone give me feedback on the performance I can expect with Microsoft OLE DB Provider for DB2 compared to std ODBC.