Hi everyone,
I have a SSIS package that pulls data from a source SQL server to a destination SQL server using a OLE DB provider for SQL Server Native Client 11.0. When I run the package in Visual Studio from the destination server everything works fine. But when I deploy the package to the SQL Server Agent Job on the destination server and run it from there, I receive the two errors below:
DFT - Task: Error: 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: "Protocol error in TDS stream".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.".
DFT - Task: Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on OLE_SCR – Task returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
When I deploy the exact same package to the SQL Server Agent Job on another target server (same subnet and same firewall rules) and run it from there, it runs flawless. So I assume the issue is not due to the source SQL server or any firewall settings.
It would be highly appreciated if someone has any suggestions that might solve this issue.
Thanks.
Destination Server: Microsoft SQL Server 2016 (SP2-CU14) (KB4564903) - 13.0.5830.85 (X64)