ADF dataflow is creating a 0 Byte file only when Source is configured to move file after completion

Question

ADF provides an option to move the source file after dataflow completion by setting up options in 'Source Settings'. We're trying to use this option to move our source file from SFTP server to an archive location (another folder on same SFTP server)

From: SFTP/source/1.csv
To: SFTP/source/archive/1.csv

The pipeline/dataflow completes without an error, but it doesn't move the source file to archive, instead it just creates a 0Bytes file in Archive folder with same name.

Please advise how can this be achieved at Dataflow level, without using Pipeline Copy activity?

Answer 1

Hi Aditya Verma ,

Welcome to Microsoft Q&A platform and thanks for posting your query here.

As per my understanding you are trying to use the "After completion" feature to move the source file after successful copy of the data in mapping dataflow. However, it's not working as expected in your case.

I tried to repro your scenario where I am trying to move the source file from one folder in ADLS container to a subfolder after successful competition of data copy and it is working as expected, unlike your case .

My observation is that we do not need to mention the file name in the 'to path ' , we just need to mention the folder/subfolder path without appending filename.

Also, kindly validate if other transformations till sink transformation ran successfully or not , and sink file got created without any issue or not. After its successful copy to sink, source file should be moved with data.

Kindly share the screenshot of entire dataflow configurations including source and sink settings. Thankyou

Hope it helps. If it resolved your issue, kindly hit on Accept answer button else kindly share more details for better help. Thankyou

Answer 2

It is a very simple dataflow which reads from SFTP host and writes to Azure SQL DB.

The data gets populated as expected in the sink db table, but source file doesn't gets moved after completion.

Please see source parameters as below:

Below is the behavior based on two options:

1. filename provided alongwith archive path in 'TO' column: Pipeline completes successfully with no errors and 0 Bytes file gets generated in archive path but source file is not deleted.
2. only Archive path provided in 'TO' column: Pipeline completes successfully with no errors but no file is generated in archive path directory and source file is also not deleted.

Answer 3

Following up on this question, I realized that its not the 0 Bytes folder, instead a folder gets created with the filename. Below is updated comments

Below is the behavior based on two options:

1. filename provided alongwith archive path in 'TO' column TO: FOLDER/SUBFOLDER/FILE
   :Pipeline completes successfully with no errors and empty folder gets generated in archive path but source file is not moved.
2. only Archive path provided in 'TO' column TO: FOLDER/SUBFOLDER
   Pipeline completes successfully with no errors but no file is generated in archive path directory and source file is also not deleted.

Answer 4

I tried few more options, and ultimately got it working by omitting the filename from 'From' option.

I.e.

From: FOLDER/SUBFOLDER

To: FOLDER/SUBFOLDER/ARCHIVE

This even worked well for Wildcard file mode option. ADF identifies the file being processed and moves from 'FROM' to 'TO' directory listed.