4

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.

Hadi
  • 36,233
  • 13
  • 65
  • 124
Ankita Potdar
  • 67
  • 1
  • 9

2 Answers2

0

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:

Community
  • 1
  • 1
Hadi
  • 36,233
  • 13
  • 65
  • 124
  • Yes sir, my data flow task size is larger than database size. Data flow task size is 10485760. – Ankita Potdar Feb 26 '20 at 10:05
  • @AnkitaPotdar you can refer to the following article to learn more about these features: https://learn.microsoft.com/en-us/sql/integration-services/data-flow/data-flow-performance-features?view=sql-server-ver15 – Hadi Feb 26 '20 at 23:18
  • @AnkitaPotdar 10485760 means 10 Megabytes – Hadi Feb 26 '20 at 23:18
  • I'm not getting you.Sorry.Is there any alternate solution? – Ankita Potdar Feb 28 '20 at 11:02
  • @AnkitaPotdar try the method described in the link mentioned at the end of my answer – Hadi Feb 29 '20 at 22:28
  • Actually the issue is not coz of large amount of data even I thought before that the issue is caused because of the data size. The DFT for other tables are running very fine with even data larger than this. – Ankita Potdar Mar 03 '20 at 12:38
  • DFT is not even able to fetch all the rows . Loading is another issue. – Ankita Potdar Mar 03 '20 at 12:40
0

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

Lorenzo Vigano
  • 21
  • 4