Share via

Protocol Error in TDS Stream in SSIS - connected host has failed to respond

Pds 46 Reputation points
2024-07-21T00:17:48.94+00:00

Hello,

We are having issue when we are running SSIS package and getting following error:

Code: 0xC0202009

   Source: Sync Details Data FLow sqlssis OLE DB Source [1]

   Description: 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: "Protocol error in TDS stream".

An OLE DB record is available.  Source: "Microsoft OLE DB Driver for SQL Server"  Hresult: 0x80004005  Description: "Protocol error in TDS stream".

An OLE DB record is available.  Source: "Microsoft OLE DB Driver for SQL Server"  Hresult: 0x80004005  Description: "Protocol error in TDS stream".

An OLE DB record is available.  Source: "Microsoft OLE DB Driver for SQL Server"  Hresult: 0x80004005  Description: "Communication link failure".

An OLE DB record is available.  Source: "Microsoft OLE DB Driver for SQL Server"  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.



I have tried to change the Provide in my Config file from SQL 
WE are not using connection manager from SSIS package but using config file.
We are connecting to Sql server hosted on AWS.
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,346 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,525 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 35,551 Reputation points
    2024-07-22T02:18:33.32+00:00

    Hi @Pds,

    Here is a same thread you may take a reference to.

    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/

    Regards，

    Zoe Hui

    If the answer is helpful, please click "Accept Answer" and upvote it.

  2. Erland Sommarskog 107K Reputation points
    2024-07-22T18:42:50.6966667+00:00

    It was running fine in On-prem but our applciation vendor now hosting Sql server into AWS which was previously we had on-prem sql server.

    That could indicate one of two things:

    1. There was an accident waiting to happen. From the error message it is difficult to tell if the message comes from SQL Server (which means that SSIS sends junk) or from the OLE DB side (which means that SQL Server is sending junk). SSIS is likely to be quite deterministic in its behaviour, but SQL Server less so, since optimisation can lead to different query plans.
    2. There is an issue in AWS networking that distorts messages.

    I think that in either case, you need to narrow down where this is happening. I think you should look in the SQL Server error log for error message coinciding with these errors in SSIS. I would also be a good idea to set up an extended events session that traps exception. I have an article on my web site to do this, and which also includes a view to query the session.

    If this is an issue with AWS, it's probably better to bring it up in an Amazon forum.