Pipeline Failure in ADF

Question

Pipeline Failure in ADF

Veizi Ioanna 10

Hi,

I have created & scheduled some pipelines in ADF and they sometimes fail and sometimes run successfully.

These pipelines include activities that I copy data from oracle db to Azure SQL db.

The error I get is that we loose connection with Azure SQL db.

Could you please advice why is this happening ?

Thanks a lot.

Konstantinos Passadis 19,686 Reputation points MVP

2023-08-01T08:12:45.9333333+00:00

Hello @Veizi Ioanna !

Welcome to Microsoft QnA!

Could you please verify that there is no Pause setting in the SQL ?

I hope this helps!

Kindly mark the answer as Accepted and Upvote in case it helped!

Regards
Veizi Ioanna 10 Reputation points

2023-08-01T08:18:44.7766667+00:00

Hi @Konstantinos Passadis,

Thank you very much for your prompt reply.

Could you please specify where exactly can I check this setting ?

Thanks a lot.
Konstantinos Passadis 19,686 Reputation points MVP

2023-08-01T08:31:29.9133333+00:00

Hello @Veizi Ioanna !
Veizi Ioanna 10 Reputation points

2023-08-01T12:34:20.75+00:00

Hi @Konstantinos Passadis ,

I checked the db and there is no Auto Pause option that I can edit.

I do not believe that this is the root cause.
Konstantinos Passadis 19,686 Reputation points MVP

2023-08-01T12:57:08.2033333+00:00

Hello @Veizi Ioanna !

Thank you for the update

In that case we are going to need additional info and details

So if possible provide some details like Error , Logs , type of Setup and messages

Basically anything that may help us understand the issue and can be published along with some history of the issue , is it new issue , it was always there from the start ? did anything changed along the way ?

Regards !
QuantumCache 20,676 Reputation points Moderator

2023-08-01T19:46:08.2966667+00:00

Hello @Veizi Ioanna,

Welcome to QnA forum,

It seems like you are experiencing intermittent connection issues, right?

Did you check the connection pool settings in the Azure SQL DB. Ensure that the maximum number of connections is set high enough to handle the load from Azure Data Factory.

Basic service tier:

In this scenario or you need to control the number of connections in the application side.

Reaching Azure SQL DB connection limits in many ways: 2020 Article, but it helps to investigate some areas of potential issue.

Also check: Query optimization: Check if the queries being executed by Azure Data Factory are optimized. Poorly optimized queries can cause high resource usage and slow down the database.

If you can provide the error message screenshot, that would be helpful to provide some suggestions///

Most of such scenarios, this requires to submit a Technical support Request with Microsoft, But do let us know if you need any help...!
Veizi Ioanna 10 Reputation points

2023-08-02T06:34:24.0433333+00:00

Hi Everyone,

Thank you very much for your assistance.

I will check also your recommendations.

The error that I receive is the following.

Operation on target load tables failed: Activity failed because an inner activity failed; Inner activity name: Copy evolution tables prod, Error: Failure happened on 'Sink' side. ErrorCode=SqlFailedToConnect,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Cannot connect to SQL Database. Please contact SQL server team for further support. Server: 'tuevpoc.database.windows.net', Database: 'db_tuv', User: 'tuevpoc_User'. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The specified network name is no longer available.),Source=.Net SqlClient Data Provider,SqlErrorNumber=64,Class=20,ErrorCode=-2146232060,State=0,Errors=[{Class=20,Number=64,State=0,Message=A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The specified network name is no longer available.),},],''Type=System.ComponentModel.Win32Exception,Message=The specified network name is no longer available,Source=,'
QuantumCache 20,676 Reputation points Moderator

2023-08-11T17:09:43.2466667+00:00

Hello @Veizi Ioanna Just checking if we are still connected on this discussion?
QuantumCache 20,676 Reputation points Moderator

2023-08-18T18:25:51.65+00:00

Hello @Veizi Ioanna Just checking if we are still connected on this discussion? Please let us know if you need to add more info so that we better assist you!
QuantumCache 20,676 Reputation points Moderator

2023-08-25T18:34:08.9966667+00:00

Hello @Veizi Ioanna Can we close this case? Please click 'Accept Answer'

2 answers

Your answer

Konstantinos Passadis 19,686 Reputation points MVP

2023-08-01T08:12:45.9333333+00:00

Hello @Veizi Ioanna !

Welcome to Microsoft QnA!

Could you please verify that there is no Pause setting in the SQL ?

I hope this helps!

Kindly mark the answer as Accepted and Upvote in case it helped!

Regards
Veizi Ioanna 10 Reputation points

2023-08-01T08:18:44.7766667+00:00

Hi @Konstantinos Passadis,

Thank you very much for your prompt reply.

Could you please specify where exactly can I check this setting ?

Thanks a lot.
Konstantinos Passadis 19,686 Reputation points MVP

2023-08-01T08:31:29.9133333+00:00

Hello @Veizi Ioanna !
Veizi Ioanna 10 Reputation points

2023-08-01T12:34:20.75+00:00

Hi @Konstantinos Passadis ,

I checked the db and there is no Auto Pause option that I can edit.

I do not believe that this is the root cause.
Konstantinos Passadis 19,686 Reputation points MVP

2023-08-01T12:57:08.2033333+00:00

Hello @Veizi Ioanna !

Thank you for the update

In that case we are going to need additional info and details

So if possible provide some details like Error , Logs , type of Setup and messages

Basically anything that may help us understand the issue and can be published along with some history of the issue , is it new issue , it was always there from the start ? did anything changed along the way ?

Regards !
QuantumCache 20,676 Reputation points Moderator

2023-08-01T19:46:08.2966667+00:00

Hello @Veizi Ioanna,

Welcome to QnA forum,

It seems like you are experiencing intermittent connection issues, right?

Did you check the connection pool settings in the Azure SQL DB. Ensure that the maximum number of connections is set high enough to handle the load from Azure Data Factory.

Basic service tier:

In this scenario or you need to control the number of connections in the application side.

Reaching Azure SQL DB connection limits in many ways: 2020 Article, but it helps to investigate some areas of potential issue.

Also check: Query optimization: Check if the queries being executed by Azure Data Factory are optimized. Poorly optimized queries can cause high resource usage and slow down the database.

If you can provide the error message screenshot, that would be helpful to provide some suggestions///

Most of such scenarios, this requires to submit a Technical support Request with Microsoft, But do let us know if you need any help...!
Veizi Ioanna 10 Reputation points

2023-08-02T06:34:24.0433333+00:00

Hi Everyone,

Thank you very much for your assistance.

I will check also your recommendations.

The error that I receive is the following.

Operation on target load tables failed: Activity failed because an inner activity failed; Inner activity name: Copy evolution tables prod, Error: Failure happened on 'Sink' side. ErrorCode=SqlFailedToConnect,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Cannot connect to SQL Database. Please contact SQL server team for further support. Server: 'tuevpoc.database.windows.net', Database: 'db_tuv', User: 'tuevpoc_User'. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The specified network name is no longer available.),Source=.Net SqlClient Data Provider,SqlErrorNumber=64,Class=20,ErrorCode=-2146232060,State=0,Errors=[{Class=20,Number=64,State=0,Message=A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The specified network name is no longer available.),},],''Type=System.ComponentModel.Win32Exception,Message=The specified network name is no longer available,Source=,'
QuantumCache 20,676 Reputation points Moderator

2023-08-11T17:09:43.2466667+00:00

Hello @Veizi Ioanna Just checking if we are still connected on this discussion?
QuantumCache 20,676 Reputation points Moderator

2023-08-18T18:25:51.65+00:00

Hello @Veizi Ioanna Just checking if we are still connected on this discussion? Please let us know if you need to add more info so that we better assist you!
QuantumCache 20,676 Reputation points Moderator

2023-08-25T18:34:08.9966667+00:00

Hello @Veizi Ioanna Can we close this case? Please click 'Accept Answer'

Answer 1

Konstantinos Passadis 19,686 MVP

Hey Ioanna !

Here is a similar thread

https://learn.microsoft.com/en-us/answers/questions/707576/how-do-i-pause-an-azure-sql-database-(serverless)

Hope this helps !

In case there is not Auto Pause we need to investigate further the issue !

Kindly mark any answer that helped as Accepted and Upvote !

Answer 2

QuantumCache 20,676 Moderator

Hello @Veizi Ioanna

Thanks for sharing the more info on the error! That was helpful to have an initial diagnosis!

The error message indicates that the pre-login handshake failed, which could be caused by a network issue or a firewall issue.
Make sure that the firewall settings for the Azure SQL Database allow the integration runtime to access the database. You can check the firewall settings in the Azure portal.

Did you try to involve anyone from your SQL Server DBA team? in your project?

Please do let us know if you need any help.

Dt:08/30/2023: Below is the Resolution Shared by Veizi Ioanna

I edited the activities in the pipelines to 'retry' in case they fail, when the disconnections occur and the 2nd time they are executed they complete successfully. For now this is the solution I have come up to.

Please click "Accept Answer" . So that we can close this case.

Veizi Ioanna 10 Reputation points

2023-08-30T06:50:58.43+00:00

Hi SatishBoddu-MSFT,

Thank you very much but the issues are not solved.

I edited the activities in the pipelines to 'retry' in case they fail,

when the disconnections occur and the 2nd time they are executed they complete successfully.

For now this is the solution I have come up to.
QuantumCache 20,676 Reputation points Moderator

2023-08-30T21:11:46.68+00:00

Thank you Veizi Ioanna for the info, Please click "Accept Answer" . So that we can close this thread.
We will reopen the case if needed.

Share via

Pipeline Failure in ADF

2 answers

Your answer