Extra empty line at the end in sink output of ADF

Question

I am creating a delimited file as output using ADF dataflow. It appends an extra empty line at the end. Can this be removed?

My source is Postgres DB. If the Postgres DB has 10 records, the sink output as 11 records with an empty line in the end.

Answer 1

Hi @Thivya Shree P Greetings! Welcome to Microsoft Q&A forum. Thank you for posting this question here.

I could validate the behavior you see in the Azure Data Factory Copy activity when copying data to a delimiter file. This behavior is caused by the rowDelimiter property on the delimiter files. For Copy activity, the single character or "\r\n" used to separate rows in a file. The default value is any of the following values on read: ["\r\n", "\r", "\n"]; on write: "\r\n".

\n stands for line feed and \r stands for carriage return. Notice that the only option for on write is \r\n, which I believe is causing an empty row at the end. Here is a reference to Dataset properties which provides details on this.

This behavior is by design. This ensures that all the data from the source is read and successfully copied to the file. With the current approach, it is not possible to circumvent the addition of this empty row.

Please note that even though there is an empty row created at the end, when you try to perform any copy actions from the file or inspect the data processed from the generated delimiter file, you can notice that the total records processed does not include the empty line at the end.

I have tested copying the generated CSV file into a test SQL server DB table and could see the correct data transferred excluding the empty row. You can view the records processed by inspecting the details of the run activity. Below is an image for your reference.

Hope this provided some clarity to the behavior you notice.

---

If the response helped, please do click Accept Answer and Yes for the answer provided. Doing so would help other community members with similar issue identify the solution. I highly appreciate your contribution to the community.

Answer 2

Hi,

In addition to the above answer, sometimes the empty line creates issues especially when we have not null constraints enabled on the downstream system that processes our file.

If you can make use of azure functions, you can get rid of this behaviour by deleting the empty row at the end using python. (Or c#).

Again, this is just a workaround that I have tried before. Best approach is to make the downstream understand the design constraint and modify the sink type to json or disable any constraint that creates issue, to get rid of this issue.

Thanks.