Hi All,

We are fetching data in to our cloud server from internal server through packages developed through import/export  wizard which are scheduled through Agent.

All these schedulers are running fine, but yesterday few schedulers failed due to TDS error.

Could someone please check the below error and suggest me the work around

Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility  Version 11.0.5058.0 for 64-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  5:30:00 AM  Error: 2015-05-19 05:46:40.74     Code: 0xC0202009     Source: Data Flow Task 1 Source - xxxxxxxxxxxxxxx [33]     Description: 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: "Protocol error in TDS stream".  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: An existing connection was forcibly closed by the remote host.  ".  End Error  Error: 2015-05-19 05:46:40.74     Code: 0xC0047038     Source: Data Flow Task 1 SSIS.Pipeline     Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on Source - DistributorMaterialGroupMapping 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.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  5:30:00 AM  Finished: 5:46:40 AM  Elapsed:  1000.67 seconds.  The package execution failed.  The step failed.

With Regards,

Ram

• Edited by ramdb55 2 hours 3 minutes ago

Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005  Description: "Protocol error in TDS stream". 

 

The issue is solved when the Network Protocol used to connect to the source SQL Server is changed from TCP/IP to NamedPipes. Basically, an alias for the source is created on the destination server using the below steps.

1. Go to Start--> All Programs--> Microsoft SQL Server 2008--> Configuration Tools --> SQL Server Configuration Manager

2. Expand the node SQL Native Client 10.0 Configuration

3. Right click on "Aliases" and then select "New Alias..."

4. Provide the IP address of the Source SQL server against the "Server" field and specify the Source server name against the "Alias Name" field.

5. Choose the protocol as "NamedPipes" and click OK    More refer:- 

 http://blogs.msdn.com/b/ramoji/archive/2009/12/30/protocol-error-in-tds-stream.aspx

https://social.technet.microsoft.com/Forums/projectserver/en-US/01e577d8-f642-4458-ba7d-4d6afbfab563/protocol-error-in-tds-stream?forum=sqldataaccess