We have a SQL Server Agent job setup to execute SSIS packages scheduled to run every 30 mins. The job runs well 98% of the time but sometimes it randomly fails with a connectivity issue trying to establish a db connection to some junk db name (it failed trying to pass variable name within db name and to a mis-spelled db name at other times). It run's alright on the subsequent run. Connection managers are setup under Environment variables.
The servers are not brought down so that can be ruled out. Also the junk db name is weird.
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 "Junkname" requested by the login. The login failed."
Did anyone come across such issue? Any input is much appreciated.
User contributions licensed under CC BY-SA 3.0