Data Load Error Using ADF V2 to Sql Server on Azure VM

Question

I'm facing below error while trying to data load from On-Prem Db2 system to Sql Server on Azure Vm using ADF V2

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.),Source=.Net SqlClient Data Provider,SqlErrorNumber=10054,Class=20,ErrorCode=-2146232060,State=0,Errors=[{Class=20,Number=10054,State=0,Message=A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.),},],

This happens only when i'm trying to load very large source table to target Sql server. Because when i changed the target to Azure SQL PaaS table got loaded successfully.

I'm not sure how to fix this data load issue and where should i start to trouble shoot this error.

Sql Server Error Log details:

" Message A fatal error occurred while reading the input stream from the network. The session will be terminated (input error: 10054, output error: 0)."

Please help here.

Note: I can't switch to Azure Sql PaaS offering as its not supporting any distributed transaction yet.

Answer 1

where should i start to trouble shoot this error.

You start with the SQL Server Error Log. This is either a network issue, or a severe server-side error.

The client error message:

A transport-level error has occurred when receiving results from the server.

and SQL Server's error message

" Message A fatal error occurred while reading the input stream from the network. The session will be terminated (input error: 10054, output error: 0)."

both agree that the network connection between the client and server failed. If your ADF Integration Runtime is running on-premises inquire about any firewalls or network security devices that might be terminating the connection.