ADF Internal server error, Not ran failed senario in case of retry failed attmept

Question

ADF Internal server error, Not ran failed senario in case of retry failed attmept

Nikunj Patel 61

Hello Team,

We had setup following pipeline as per screenshot. main goal of this pipeline is copy data from sql server to data lake blob storage using parquet file type.

Many times we facing Internal server error in Copy activity, but we setup retry attempt to 3 to make it successful. But in case of failure attempt Copy activity created parquet file on data lake with leased stage. To handle this we created one pipeline which drop this type of files in case of failed copy activity.

But we notice like when copy activity failed using following internal error in first 2 attempt and in last attempt in was successful. Still it not ran failed scenario and not drop those leased files in 2 failed attempt.

"errorCode": "1000",
"message": "ErrorCode=SystemErrorActivityRunExecutedMoreThanOnce,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The activity run failed due to service internal error, please retry this activity run later.,Source=Microsoft.DataTransfer.TransferTask,'",
"failureType": "SystemError",
"target": "Move_Raw_To_Bronze",
"details": []

So can you please guide us is there any specific reason why it not ran first 2 failed attempt ? (In my case copy activity successes in 3rd attempt).

Thank you in advance.

0 comments

Answer accepted by question author

Pratik Somaiya 4,211 Volunteer Moderator

Hello @Nikunj Patel

The retry attempt is of Copy activity right, so it will retry 3 times and based on the result of 3rd retry it will go into either success or failed state

That is why it didn't delete the files as the process was still executing copy activity

On the error side, mostly internal server issues happen due to network or server issues

In your case, as SQL Server is used, so there might be a problem with either Integration Runtime or On-Premises network

You need to check if both are working fine and in running state during the execution. You can monitor IR when you begin execution of the pipeline

Nikunj Patel 61 Reputation points

2022-03-28T04:56:28.967+00:00

Thank you @pratik mehta for your input. This internal server error scenario not happened every time and also its was not reproducible. Because of this reason we set up retry attempt to make pipeline success.

Can you please guide me how I can drop those parquet files in case of failed attempt? In failed attempt copy activity created leased file on data lake and that cause pipeline failed in next steps which read parquet files by other pipeline.
Pratik Somaiya 4,211 Reputation points Volunteer Moderator

2022-03-28T07:12:52.057+00:00

Ideally leased files gets deleted on its own if the activity fails, is that not the case?
Nikunj Patel 61 Reputation points

2022-03-28T07:38:55.3+00:00

it keep exists with 0 MB size.
Pratik Somaiya 4,211 Reputation points Volunteer Moderator

2022-03-28T08:03:28.727+00:00

Then in the failure output section of Copy Activity you can use a delete activity to delete the blobs, but as the blob is in leased state, you will have to check if the delete activity can delete it
Nikunj Patel 61 Reputation points

2022-03-28T09:05:11.387+00:00

I did the same . See above screenshot in question. But failure output not ran in failed attempt.
In my case first 2 attempt failed and last attempt successfully completed So failed scenario not ran and leased files not deleted.
Pratik Somaiya 4,211 Reputation points Volunteer Moderator

2022-03-28T09:56:12.71+00:00

I would then suggest to not keep the retry configuration in your case, as you said the issue happened once, without retry count it should be fine, let the copy fail which would fail the pipeline, so that when you rerun you can start from fresh
Nikunj Patel 61 Reputation points

2022-03-28T10:44:19.967+00:00

you have any idea how we can read all blob files with 0kb size using ADF?

my plan is once copy activity completed then I can add one more check like if any blob which size is 0kb then drop all those blobs. In this way I can fix this .

Also thank you so much for keep support on this thread.
Pratik Somaiya 4,211 Reputation points Volunteer Moderator

2022-03-28T11:48:32.72+00:00

@Nikunj Patel You can try suggestion given in this link to get the file size, if you think my response helped then please Mark it as an Answer and Upvote :)

https://stackoverflow.com/questions/64142654/azure-data-factory-grab-file-from-folder-based-on-size
Nikunj Patel 61 Reputation points

2022-03-28T12:32:09.8+00:00

Below link give more depth and detail way. I tried same things but again I was stuck because ADF not able to delete Leased file. you have any solution to delete leased file or we can break the lease before delete file using API?

https://stackoverflow.com/questions/65152580/azure-data-factory-v2-check-file-size-for-copy-activity

3 additional answers

Your answer

Nikunj Patel 61 Reputation points

2022-03-28T04:56:28.967+00:00

Thank you @pratik mehta for your input. This internal server error scenario not happened every time and also its was not reproducible. Because of this reason we set up retry attempt to make pipeline success.

Can you please guide me how I can drop those parquet files in case of failed attempt? In failed attempt copy activity created leased file on data lake and that cause pipeline failed in next steps which read parquet files by other pipeline.
Pratik Somaiya 4,211 Reputation points Volunteer Moderator

2022-03-28T07:12:52.057+00:00

Ideally leased files gets deleted on its own if the activity fails, is that not the case?
Nikunj Patel 61 Reputation points

2022-03-28T07:38:55.3+00:00

it keep exists with 0 MB size.
Pratik Somaiya 4,211 Reputation points Volunteer Moderator

2022-03-28T08:03:28.727+00:00

Then in the failure output section of Copy Activity you can use a delete activity to delete the blobs, but as the blob is in leased state, you will have to check if the delete activity can delete it
Nikunj Patel 61 Reputation points

2022-03-28T09:05:11.387+00:00

I did the same . See above screenshot in question. But failure output not ran in failed attempt.
In my case first 2 attempt failed and last attempt successfully completed So failed scenario not ran and leased files not deleted.
Pratik Somaiya 4,211 Reputation points Volunteer Moderator

2022-03-28T09:56:12.71+00:00

I would then suggest to not keep the retry configuration in your case, as you said the issue happened once, without retry count it should be fine, let the copy fail which would fail the pipeline, so that when you rerun you can start from fresh
Nikunj Patel 61 Reputation points

2022-03-28T10:44:19.967+00:00

you have any idea how we can read all blob files with 0kb size using ADF?

my plan is once copy activity completed then I can add one more check like if any blob which size is 0kb then drop all those blobs. In this way I can fix this .

Also thank you so much for keep support on this thread.
Pratik Somaiya 4,211 Reputation points Volunteer Moderator

2022-03-28T11:48:32.72+00:00

@Nikunj Patel You can try suggestion given in this link to get the file size, if you think my response helped then please Mark it as an Answer and Upvote :)

https://stackoverflow.com/questions/64142654/azure-data-factory-grab-file-from-folder-based-on-size
Nikunj Patel 61 Reputation points

2022-03-28T12:32:09.8+00:00

Below link give more depth and detail way. I tried same things but again I was stuck because ADF not able to delete Leased file. you have any solution to delete leased file or we can break the lease before delete file using API?

https://stackoverflow.com/questions/65152580/azure-data-factory-v2-check-file-size-for-copy-activity

Answer 1

Srikanth Thota 6 Microsoft Employee

Hello @Nikunj Patel

If we leased the files we can pass the lease id through headers in ADF and we can delete but in your case the files are automatically leased when they created( if my understanding is correct) I am not sure how to pass those leased id. If you got the solution please feel free to share here.

Alternatively, you can send the size greater than zero files to other container( Using Getmetadata, If and copy activity inside If activity) and consume these files in the next pipeline to avoid interruptions in the flow. Later you can delete the container which contain leased files and normal files manually.

Please let me know how it goes?

Regards
Srikanth Thota

Nikunj Patel 61 Reputation points

2022-03-31T09:18:59.363+00:00

Ok this answer will make sense. Last question on this thread Is there any way we can drop the container using ADF?

Also you said "If we leased the files we can pass the lease id through headers in ADF and we can delete " So can you please provide any reference link for same?
ShaikMaheer-MSFT 38,631 Reputation points Microsoft Employee Moderator

2022-04-04T16:33:32.527+00:00

Hello @Nikunj Patel ,

We can use Delete activity to delete containers in ADLS.

-------
Please consider hitting Accept Answer button. Accepted answers help community as well.

Answer 2

Srikanth Thota 6 Microsoft Employee

Hello @Nikunj Patel , As far as I know you cant delete the leased files. In your case, you can save all the 0 MB files to one container and delete that container. No matter whether it is leased or not it will be deleted.

Let me know if am wrong. Thanks

Nikunj Patel 61 Reputation points

2022-03-30T09:47:42.28+00:00

@ssrikanththota-0662 for your input. you are correct we can delete the container or folder without any matter of files is leased or not. But How can I move all 0kb files into other container using ADF. If you have any way then please let me know. thank you in advance.

Answer 3

ShaikMaheer-MSFT 38,631 Microsoft Employee Moderator

Hi @Nikunj Patel ,

Thank you for posting query in Microsoft Q&A Platform.

As per my understanding you want have a process which deletes 0MB files once your copy activity execution completes (When I say complete it can complete as success as failure). Please correct me if I am wrong.

I can see your delete 0MB files process was encapsulated into a separate pipeline. So, you should consider running that execute pipeline activity on completion.

Please check below screenshots which explains logic implementation on high level.

To know more about GetMetaData activity click here.

Hope this helps. Please let us know if any further queries.

--------------

Please consider hitting Accept Answer button. Accepted answers help community as well.

Nikunj Patel 61 Reputation points

2022-03-29T15:16:53.42+00:00

Thank you @ShaikMaheer-MSFT for your response.

If you see in above comment I did same steps using below link. but I was stuck , ADF not able to delete Leased file. in my case copy activity created leased file.

https://stackoverflow.com/questions/65152580/azure-data-factory-v2-check-file-size-for-copy-activity

If you have any idea How to delete leased file then let us know. thank you in advance.
ShaikMaheer-MSFT 38,631 Reputation points Microsoft Employee Moderator

2022-03-29T16:21:04.923+00:00

Hi @Nikunj Patel , In your case you connected delete task with red line from Copy activity. That means only when copy activity fails then delete works.

You should consider connecting blue line from Copy activity to delete task, which make sures to run delete task in both cases of copy activity success or failure. Same mentioned in above answer image.

Please check below to get idea how to get blue link.

Hope this helps. Please let us know if any further queries.

-----------

Please consider hitting Accept Answer button. Accepted answers helps community as well.
Nikunj Patel 61 Reputation points

2022-03-29T16:43:20.127+00:00

I understand what you said. but my concern is Delete file pipeline not able to delete file which were in leased stage and pipeline were failed again. How we can delete leased file using ADF?