Exporting Data to AS400 from SQL2005 with SSIS is SLOWWWWW
-
Rick,
I've tried all of the same connections that worked for you, with the exception of iSeries Access for Windows ODBC data source. I think this requires SQL Enterprise or Dev edition to run. We're running Standard. All of these have extremely poor performance.
I've spoken with one of our AS400 programmers and he has run into this multiple time before as well. He confirmed with me that the row by row method is the issue.
If I don't figure out something soon, I'm just going to provide a flat file to the IFS on the AS400 and let the programmer deal with getting the data into a database file.
I'll still keep you posted. Thanks for your comments.
-
Brad Johnson-378105 (1/14/2010)
Rick,I've tried all of the same connections that worked for you, with the exception of iSeries Access for Windows ODBC data source. I think this requires SQL Enterprise or Dev edition to run. We're running Standard. All of these have extremely poor performance.
I've spoken with one of our AS400 programmers and he has run into this multiple time before as well. He confirmed with me that the row by row method is the issue.
If I don't figure out something soon, I'm just going to provide a flat file to the IFS on the AS400 and let the programmer deal with getting the data into a database file.
I'll still keep you posted. Thanks for your comments.
Brad,
Did you succeed setting up "IBM DB2 .NET Data Provider" to work with your AS400? If yes, can you try CozyRoc DB2 Destination component and let us know what performance do you get?
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
I have not gotten it to work yet, but I did figure out that I had to include the port number with the servername i.e. KRMSDEV:446. The default port for AS400/iseries is 446. After doing this, I can connect but get a licensing error:
"An attempt to connect to a host failed due to a missing DB2 Connect product or invalid license. SQLSTATE=42968"
I've sent the error to our AS400 operations person to see if they can shed any light onto this.
Please let me know how you've progressed.
-
Brad Johnson-378105 (1/28/2010)
I have not gotten it to work yet, but I did figure out that I had to include the port number with the servername i.e. KRMSDEV:446. The default port for AS400/iseries is 446. After doing this, I can connect but get a licensing error:"An attempt to connect to a host failed due to a missing DB2 Connect product or invalid license. SQLSTATE=42968"
I've sent the error to our AS400 operations person to see if they can shed any light onto this.
Please let me know how you've progressed.
Brad,
Did you see this?
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
Back to square one. I didn't realize that DB2 Connect was a purchased application and necessary for the .NET provider to work. Oh well...
-
Brad Johnson-378105 (1/28/2010)
Back to square one. I didn't realize that DB2 Connect was a purchased application and necessary for the .NET provider to work. Oh well...Brad,
You have to contact IBM and find out if that is the case. This could be a bug.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
Can you please post the steps to configure SSIS package to invoke fastexport utility for bulk loading from teradata to sql server 2005? Also please let me know which type of database connectivity is required for using fastexport in SSIS?
It will be really helpfull as I am new to SSIS..
Regards,
Sumit
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply