SSIS package fails with Protocol error in TDS stream

  • Article

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.