Link to home
Start Free TrialLog in
Avatar of infotechelg
infotechelgFlag for United States of America

asked on

SSIS ODBC SQL to DB2 Data Transfer Failing

I have an SSIS job that sends data from SQL Server to a DB2 on an iSeries using ODBC. In the ODBC Destination task, the "Batch size" is set to 1000. There are 166,103 records to transfer.

When I run the job, it errors out after transferring 165,280 records (823 didn't make it over).

If I change the "Batch size" to 5000, it errors out after transferring 162,280 records (3823 didn't make it over).

If I change the "Batch size" to 10,000, it errors out after transferring 157,280 records (8823 didn't make it over).

Why is the "Batch size" affecting the transfer? It doesn't make sense.

Here are the errors:

Error: 0x384 at Add to BOSS, BOSS Destination [2]: Open Database Connectivity (ODBC) error occurred. SQLExecute returned error while inserting row 157281

Error: 0x384 at Add to BOSS, BOSS Destination [2]: Open Database Connectivity (ODBC) error occurred. state: '22018'. Native Error Code: 30200. [IBM][iSeries Access ODBC Driver]Column 2: CWBNL0107 - Converted 60 bytes, 1 errors found beginning at offset 14

Error: 0x384 at Add to BOSS, BOSS Destination [2]: Open Database Connectivity (ODBC) error occurred. state: '22018'. Native Error Code: 30019. [IBM][iSeries Access ODBC Driver]Error in assignment.

Error: 0xC0047022 at Add to BOSS, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "BOSS Destination" (2) failed with error code 0x80004005 while processing input "ODBC Destination Input" (13). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

Note that one of the errors seems to indicate there are duplicate keys. That is not the case.

Any  help you can provide would be appreciated.
ASKER CERTIFIED SOLUTION
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of infotechelg

ASKER

Thanks, i'll give that a shot.

- Jeremy