Intermittent connection error (ODBC) from iSeries/DB2 after upgrade from 2012 to 2019

Question

We're in the process of upgrading from SQL Server 2012 to 2019 and I'm testing out our SSIS packages. I'm getting an intermittent connection error with the packages that import data from our iSeries/DB2 and searching hasn't brought back anything that's helped.

We're connecting through ODBC (we've got the iSeries Access ODBC driver version 13.00.01.00 installed on the server and on my local machine, 32-bit).

The connection manager tests out okay in Visual Studio, and the following behavior is happening both on my local machine through Visual Studio and running from the Integration Services Catalog on the server. I'm using the same credentials for the iSeries in both places.

The error I'm seeing is

There are no further error messages, just that one repeating for each import that fails.

Everything I've found regarding that error points to either incorrect credentials or a 32-bit/64-bit mismatch between the ODBC DSN and how the package is being run.

All data flow tasks in the package use the same connection manager to the source and I'm only receiving the error maybe half the time, so it doesn't seem to be a credentials issue.

I'd originally created the DSN in 64-bit ODBC Data Sources (which didn't work at all), deleted that and recreated in 32-bit ODBC Data Sources, and switched the packages to use 32-bit runtime, at which point the errors below started showing up:

The container fails because the data flow task fails (we use checkpoints so everything not directly on the workspace is set to fail parent on failure)

All objects in the data flow tasks succeed:

And the execution results show (as above in the first image):
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on (table) returned error code 0x80004005. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

I'm not sure what to try next. I haven't been able to find anything regarding compatibility between the ODBC driver we're using and SQL Server 2019, which would be my initial thought.

I'm hoping someone has run into a similar issue and has figured out a fix for it.

Thanks in advance for your help!

Answer 1

Hi @Jennifer Levy (she/her) ,

The error message is a bit hard for us to narrow down the issue.

Please make sure the target SQL Server version is match with your SQL Server.

If you create a new package, add the ODBC Data Sources and the destination, will it success? If you deploy the package to the catalog and run in SSMS, will it run well?

In the iseries-import-foreach-loop, could you please collect the error message in execution result when you run the package.

I also did some online research for you and did not find any useful information.

I'll do some test locally to see if I could re-produce the issue.

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Update: Our DBA and I tried setting up a linked server to the iSeries using ODBC and couldn't. After looking at documentation in various places, I was able to set up a linked server using OLE DB, so I tried switching the SSIS package to use an OLE DB source instead of an ODBC one. I got better error reporting from that, and the one table I'd been testing with that had been failing consistently had a timestamp-type column on iSeries that the package was suddenly not translating into a DATETIME. I've done that manually in a Derived Column transformation on the results of the OLE DB source query and it just completed successfully.

I've just tried to do the same on the ODBC source query and see if that was the issue and the ODBC connections just don't give robust enough error messages. The ODBC connection is retaining the timestamp data type rather than the OLE DB one that translates it to a VARCHAR. Debating switching everything over to OLE DB rather than sticking with ODBC like we'd originally done.