通过

SSIS package fails with Protocol error in TDS stream

  • 项目

Encountered this error while running a SSIS package through a SQL Agent Job on SQL Server 2008. The processing in the package is a pretty simple and the purpose is to export data from few tables from one SQL Server to another. Both the source and target servers are on SQL Server 2008 (10.0.2531) and Data Flow Task (with OLEDB source and destination tasks) is used for data export. The package is created using Visual Studio 2008 and the job is running on the destination server.

The job continuously failed with the following error and in each run the failure occured at a different table.

Source: DFT_MyTable Source - MyTable

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 10.0" Hresult: 0x80004005

Description: "Protocol error in TDS stream". An OLE DB record is available

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

Please drop me a note if you have come across this error and this blog has helped you in resolving the issue.

Comments

  • Anonymous
    October 13, 2010
    That solved our problem, thanks! We are running a 32-bit application with SQL Server 2008 on Windows Server 2008 R2 (64 bit).

  • Anonymous
    October 04, 2011
    Not only did this not work, SSIS now refuses to end the job and stays yellow until I click the stop button. ***.

  • Anonymous
    October 04, 2011
    The comment has been removed

  • Anonymous
    July 31, 2012
    This solved a problem where we were trying to connect to a source server across a firewall. Without the alias the transfer task was able to move precisely 100 rows at a time and no more without crashing. Source is SQL 2005/Server 2003 destination is 2008 R2. Thanks for the help. I suspect the dude above just didn't wait long enough for the job to complete.

  • Anonymous
    November 15, 2012
    I ran into this problem using linked-server queries.  I solved it by explicitly CASTing VARCHARs, e.g., CAST(fieldname AS VARCHAR(30)) AS FIELDNAME.  The sproc itself worked fine without CASTing when executed in SSMS, but I got the "Protocol error in TDS stream" when using it in OLE DB data source in a Data Flow Task.  Adding the CAST eliminated the problem.

  • Anonymous
    June 18, 2013
    I followed the Steps but error occurred again.

  • Anonymous
    November 13, 2013
    :-( .... Followed the steps. But still i am getting this error ...

  • Anonymous
    March 23, 2014
    Thanx a lot, i solved my problem. :-)