There are three SQL Servers involved in this error.
1 Application Server
SQL Server 2016 Standard
2 SSIS Server
SQL Server 2016 Enterprise
Running SSIS and hosting SSISDB
3 Data Warehouse
SQL Server 2016 Enterprise
An SSIS package is executed on Server #2 which pulls data from Server #1 and writes it to Server #3. Server #2 runs many packages like this pulling data from many different systems (SQL, Excel, CSV, SQLite, etc.).
We are having intermittent errors when trying to copy data from Server #1, no other data flow is impacted, only those that query Server #1.
The Errors are always the exact same, regardless of the tables being queried and they all happen on the OLEDB Source.
Errors:
OLEDB Source failed validation and returned error code 0xC020801C
Data Flow: Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "ConnectionManagerName" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
PackageName: Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.
Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Driver for SQL Server" Hresult: 0x80004005
Description: "Client unable to establish connection".
An OLE DB record is available. Source: "Microsoft OLE DB Driver for SQL Server" Hresult: 0x80004005
Description: "TCP Provider: An existing connection was forcibly closed by the remote host."
I haven't found anything in the SQL Logs of either server and I'm using the most up to date OLEDB drivers available.
I ran a trace on the server with the Networking team and we identified that when the error happens, there is no traffic registered by Server #1.
Any advice or guidance with this error would be greatly appreciated.
-M3mori3s