Search code examples
azure-data-factory

Azure Data Factory Permission Issue when copying to Parameterized Folder on SFTP Server

When copying to the ROOT folder on SFTP Server without any parameters as shown in the image the copy activity is successful. T enter image description here

As you can see the file copies to the root no problems

enter image description here

However, when I try and copy to a dynamic folder (in this the dynamic folders are based on the time the copy activity happened) for example RAW\time_folder, see image, you can see there isn't any data in the folder.

enter image description here

enter image description here

When parameterizing the connection to a dynamic directory the configuration/parmeters are as follows:

enter image description here

enter image description here

The actual parameter to create the directory and send file to the directory is as follows:

@{pipeline().parameters.Zone}/@{pipeline().parameters.Time}

So, can someone let me know where the above parameters will successfully create the directory (in this case the dynamic time based directory), but not copy/send data to the directory?

Following the help I've received, I'm updating question with the error I'm getting after applying the suggestions, see below

enter image description here

The full error message is as follows:

ErrorCode=SftpPermissionDenied,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Permission denied to access 'RAW/02-47/xxxxxxxx_20230613_20230613025522.csv.f95d11f792884fb888f7e535f0fb1a6e'.,Source=Microsoft.DataTransfer.ClientLibrary.SftpConnector,''Type=Renci.SshNet.Common.SftpPermissionDeniedException,Message=Permission denied,Source=Renci.SshNet,'

The Expression for desTable is as follows:

@concat(
    pipeline().parameters.TableName,
    '_',
    formatDateTime(utcNow(),'yyyyMMdd'),
    '_',
    formatDateTime(utcNow(),'yyyyMMddhhmmss'), '.csv'
    )

enter image description here

Solution

  • When the value for dataset parameter is given as @concat(pipeline().parameters.Zone,'/',pipeline().parameters.Time) instead of @{pipeline().parameters.Zone}/@{pipeline().parameters.Time}, file is copied without error.

    enter image description here

    enter image description here

    Update:

    If you receive the error "UserErrorSftpPathNotFound," "UserErrorSftpPermissionDenied," or "SftpOperationFail" when you're writing data into SFTP, and the SFTP user you use does have the proper permissions, check to see whether your SFTP server support file rename operation is working. If it isn't, disable the Upload with temp file (useTempFileRename) option and try again. To learn more about this property, see the preceding table. If you use a self-hosted integration runtime for the Copy activity, be sure to use version 4.6 or later.

    Reference: MS document on Copy and transform data in SFTP server using Azure Data Factory or Azure Synapse Analytics