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 (8)

  1. Jennie says:

    That solved our problem, thanks!

    We are running a 32-bit application with SQL Server 2008 on Windows Server 2008 R2 (64 bit).

  2. Anon says:

    Not only did this not work, SSIS now refuses to end the job and stays yellow until I click the stop button.

    ***.

  3. Anon says:

    Not only did this not work, SSIS now refuses to end the job and stays yellow until I click the stop button.

    Arsehole.

  4. Michael says:

    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.

  5. christian_bahnsen says:

    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.

  6. Vijetha says:

    I followed the Steps but error occurred again.

  7. Tanmoy says:

    🙁 …. Followed the steps. But still i am getting this error …

  8. Ratnakar says:

    Thanx a lot, i solved my problem. 🙂

Skip to main content