We're in the process of upgrading from SQL Server 2012 to SQL Server 2019. I'm in charge of getting all of our SSIS packages upgraded and tested and I've run into a really odd issue (fortunately this is still only on our dev server so I've got some breathing room, but we're aiming for the first week of February to have production upgraded, so only a little breathing room).
We've got a handful of packages that import data from DB2 on iSeries and the upgraded packages are throwing connection errors like this one (the execution reports show no additional error messages):
Searching for information about the error code comes back with either the credentials are incorrect or the package is running in 64-bit mode and needs to be running in 32-bit mode.
The errors are intermittent; all data flow tasks are using the same connection manager to the source but only a few of the dozen tables throw this error, and I'll see this error but later see that the data flow task was able to write rows to the destination (the table I used above later shows that it wrote 9872 rows to the destination), so the credentials don't seem to be the problem.
Steps I've taken:
- Tried using a 64-bit ODBC DSN on the server and running the package in 64-bit mode, nothing connected at all
- Replaced the DSN with a 32-bit DSN with the same name
- Changed the SSIS package to run in 32-bit mode (at this point, I started getting the intermittent error; quite a bit was loading without error)
- Verified that the connection works in Visual Studio using the same credentials as are in the package configuration (I have not run the full package from my machine because it's enormous and my connection over the VPN is mediocre at best)
If anyone has any suggestions, I would greatly appreciate them.
If I can provide any additional information, please let me know.
Thanks in advance!
Jennifer Levy (@iffermonster)