SSIS Data Flow Task Errors : SSIS Error Code DTS_E_OLEDBERROR and SSIS Error Code DTS_E_PRIMEOUTPUTFAILED

3
votes

In my Data Flow Task (DFT ), I have OLE DB Source and Destination . In connection manager, my connection is OLE DB too. Source is pulling 80000 records and loading in Desti. Simple package.

My Package is breaking on OLE DB Source. The point to note is : It loads 53769 records very fine. But at the end it breaks on OLE DB Source.

Error:

[OLE DB Source [235]] Error: 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: "Communication link failure". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Shared Memory Provider: No process is on the other end of the pipe.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on OLE DB Source 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.

Can anyone help me please to solve this error? This is my production environment.

2
sql-serverssisetldataflowtask

2 Answers

0
votes

Communication link failure

Shared Memory Provider: No process is on the other end of the pipe

The error messages above imply that there may be some network issues between the servers or the machine resources (especially RAM) are not able to handle the data.

You can try to change the adjust the data flow task buffer size.

Or you may try to load data in chunks, you can refer to the following article for a step-by-step guide:

0
votes

I fixed it changing the Provider from SQL Server Native Client to Microsoft OLEDB Provider for SQL Server - it worked like a charm.