I have 3 scheduled packages which are identical except for the connection manager names, as they connect to different databases. I have created dtsx configuration files for these packages which again are identical except for the connection manager name used, and the connection string details (initial catalog). They all use the same user id and password to connect to the databases (one is iSeries (AS/400) for data input, one is SQL Server for data output). Two of the jobs work fine (they connect to the same iSeries test database but different SQL test databases). The 3rd job sometimes works, sometimes fails. It connects to production databases on both ends. The error generated is
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. End Error Error: 2008-01-25 05:45:04.86 Code: 0xC020801C Source: Copy AS400 Account Balances to Tempest USEBALP  Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "NWIPDTA" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2008-01-25 05:45:04.86 Code: 0xC0047017
All jobs run as SQL Agent Service Account, so they have the same credentials.
I have checked the iSeries logs and nothing else is running that could lock out the connection attempt. But it does show a connection being serviced from the SQL Server.
Any ideas why it works one day and fails another?
I can log into SSMS as the SQL Agent Service Account, and run the job from the SQL Server Agent jobs list, and it works fine from there.