Copy Activity SFTP sometimes copies files and sometimes does not

Question

I run once a day a pipeline that has a 'Get Metadata' returning Child items and I filter the result to CSV type.

The result is then past to a For Each which has a Copy data task and for each file @item().name it picks up the file from an SFTP and puts into an Azure Blob.

On the same parameters the pipeline can sometimes fail on all files, success on some and fail on others or fail on all i.e.

example from today: pipelines

both files failed: b2006ed8-33c4-45ce-be26-4374ede2559e

1 file success, 1 file failed: 24f93f65-8ec9-40a5-903b-fb0f289e7893

both files success: d4094c3b-5362-41c4-b12c-8cb444fa38fe

I have the error message: SftpFailedToConnectToSftpServer,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Failed to connect to Sftp server

Has anyone else experienced this?

ideas on how to resolve?

Answer 1

Hi @Jay

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer .

Issue:
I run once a day a pipeline that has a 'Get Metadata' returning Child items and I filter the result to CSV type.

The result is then past to a For Each which has a Copy data task and for each file @item().name it picks up the file from an SFTP and puts into an Azure Blob.

On the same parameters the pipeline can sometimes fail on all files, success on some and fail on others or fail on all i.e.

example from today: pipelines

both files failed: b2006ed8-33c4-45ce-be26-4374ede2559e

1 file success, 1 file failed: 24f93f65-8ec9-40a5-903b-fb0f289e7893

both files success: d4094c3b-5362-41c4-b12c-8cb444fa38fe

I have the error message: SftpFailedToConnectToSftpServer,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Failed to connect to Sftp server

Has anyone else experienced this?

ideas on how to resolve?

Solution:
The job has now been running a few days and it working fine.

The answer was: setting enabled Sequential in my ForEach activity.

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

Hope this helps. Do let us know if you any further queries.

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

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 2

You can use this link to troubleshoot your issue : https://learn.microsoft.com/en-us/azure/data-factory/connector-troubleshoot-ftp-sftp-http

Fluctuations in network connectivity between your Azure environment and the SFTP server can cause intermittent failures. You might want to look into network logs or monitoring tools to see if there are any noticeable drops or instability in network traffic when these failures occur.

The SFTP server might be under heavy load at times or might not handle multiple concurrent connections well. Check if the server has load limits or if there are any performance metrics that indicate the server is struggling when multiple files are being accessed.

Answer 3

Hi - thank you for the document link. I think the issue is multiple connections. I tried setting concurrent to 1 but it still failed. I have now set enabled Sequential in my ForEach activity and this looks like it has done the trick but I need to run it a few days to make sure it has been resolved.