Export To Database Table Error

0
votes

hope someone can give me a tip on this one. I'm trying to export query results from a database in one server to a table in a database in another server. The Export Wizard transferred a couple hundred thousand rows and then threw the errors below:

Errors:

Error 0xc0202009: Data Flow Task 1: 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. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Protocol error in TDS stream". 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. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Communication link failure". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" 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. ". (SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - Query" (1) 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. (SQL Server Import and Export Wizard)

1
sql-serverssis

1 Answers

0
votes

Protocol error in TDS stream can be attributed to perhaps a flaky network connection or an isolated network jam. There may be a networking issue between Source and Destinaion.TDS stands for Tabular Data Stream and is the underlying protocol that SQL Server uses to return results to clients. The problem is with SQL Server or with the network communications. Possibly a faulty network card in one of the computers or an issue with the networking hardware in-between them. Check the event logs on both computers for networking errors at the OS level. Also check firewall setting if you are using load balancing. Transferring big table over slow network will cause problem.

If you are using linked-server queries then try explicitly casting VARCHARs, e.g., CAST(fieldname AS VARCHAR(30)) AS FIELDNAME. Sometime adding the CAST eliminate the problem.

Also give a try by following below.

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

There was a bug which was causing TDS error, so make sure that you have the latest updates/patches/hotfix for you SQL server.