SSIS packages deployed on SSIDB are scheduled through a SQL Agent job. The package ran with no issues when only one job is scheduled for it. Later when we scheduled another job to run the same package with different parameters, the job randomly fails with connectivity error. As you can see from the below message, instead of passing the parameter value, the parameter name is referred to in Database.
SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Destination" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Login failed for user 'NT SERVICE\SQLSERVERAGENT'.".An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Cannot open database "Server_N" requested by the login. The login failed."
P.S. There is no overlap between these jobs.
User contributions licensed under CC BY-SA 3.0