Hi,
I'm trying to transfer data from a DB2 database to a MS SQL Server database (approx. 150 000 rows).
When I execute the package, the process is always cancelled (In output window: SSIS package "DB2_test_TranAddl.dtsx" finished: Canceled.)
I use a view on DB2 to provide the data and I can modify it to return only a few rows. To achieve this, I use a WHERE clause: WHERE OUTLET_NO Between X And Y.
When I try OUTLET_NO Between 1 And 5 (255 rows) the package executes successfully.
When I try OUTLET_NO Between 5 And 10 (388 rows) it works.
When I try OUTLET_NO Between 1 And 10 (643 rows) it fails!!
Any idea?
How fast does each of the queries run (1-5 and 5-10)? Suppose the connector is timing out?|||
Phil Brammer wrote: How fast does each of the queries run (1-5 and 5-10)? Suppose the connector is timing out?
A few seconds (less than 5)
The timeouts are set to 0.
I tried changing them to 60 seconds but it doesn't work...
|||OK here's an update:
I was using the IBM driver, it is not functionnal.
I installed the Microsoft OLE DB provider for DB2 and (after spending like half an hour trying to configure the connection) it works. 150 000 rows transferred successfully in a little more than 8 minutes. Takes about 1min30sec. with the DTS version so I guess I have to do some tweaking...
EDIT: I use the FastLoad on the destination. Takes approx 1min30sec.
|||Fleo,
I have the same performance issue you mentioned. 300,000 rows takes only 3 minutes with the ODBC connection in DTS, but more than 5 minutes with the microsoft OLD DB provider for DB2 in SSIS. Have you figured out any tweaking that may help speed up the transfer?
Thanks.
|||Any reason you can't use an ODBC connection in SSIS?
-Jamie
|||I need to load data from SQL2005 to AS400 and there is no ADO.NET/ODBC destination. Also, the unicode issue.|||
Jane2006 wrote:
I need to load data from SQL2005 to AS400 and there is no ADO.NET/ODBC destination. Also, the unicode issue.
Right, well, let's keep your issue to the thread you started: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1723508&SiteID=1
You can use the OLE DB Destination to write to AS400.
No comments:
Post a Comment