Protocol Error in TDS Stream SSIS when running with SQL Server Agent Job

Gerben 96 Reputation points
2020-09-23T08:05:01.867+00:00

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)
26863-ssis-error.png

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
1,859 questions
No comments
{count} votes

Accepted answer
  1. Gerben 96 Reputation points
    2020-09-25T15:42:45.477+00:00

    Thanks all for the support.

    Eventually the solution was to change the ‘RetainSameConnection’ property from 'false' to 'true'. This can be done both in the SSIS package under connection manager properties and in the job step properties (Configuration > Connection Managers).

    Link: http://www.sqlerudition.com/what-is-the-retainsameconnection-property-of-oledb-connection-in-ssis/

    I'm still not sure what the cause of the issue was but changing this setting seems to create a more stable connection with the data source server.

    Regards Gerben

    No comments

2 additional answers

Sort by: Most helpful
  1. Monalv-MSFT 5,686 Reputation points
    2020-09-23T09:55:07.283+00:00

    Hi @Gerben ,

    The following links will be helpful:
    Protocol error in TDS Stream
    Error: "SSIS Error Code DTS_E_PRIMEOUTPUTFAILED"
    SSIS.Pipeline Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on OLE DB Source returned error code 0xC0202009

    Best Regards,
    Mona


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


  2. Yitzhak Khabinsky 20,021 Reputation points
    2020-09-25T12:09:37.507+00:00

    HI @Gerben ,

    Both providers you are using: SQLOLEDB and SQLNCLI - are deprecated.
    It is better to start using newer Microsoft OLE DB Driver for SQL Server: MSOLEDBSQL.

    Here is the link: oledb-driver-for-sql-server

    "...Important

    The previous Microsoft OLE DB Provider for SQL Server (SQLOLEDB) and SQL Server Native Client OLE DB provider (SQLNCLI) remains deprecated and it is not recommended to use either for new development work..."

    No comments