space charecter issue in sink

Question

Hi ,

When I am trying to load the data from on-prem db to azure blob storage , I am getting single row splitting to two or three rows like below in the sink file after the copy activity.

My sink setting is like below.

Can you please let me know how I can write the data in a single line instead of multiple lines ?

Thanks

Answer 1

Hi @Gowri Shankar ,

settings you have at the sink Dataset looks good to me. Wondering probably you have "Word Wrap" option selected in the app where you are viewing the file (please see the screenshot below). If the option was selected, just de-select it. :)

Hope this helps. If it does, please don't forget to "accept the answer" and "up-vote"! Thank you!

Answer 2

Hi @Gowri Shankar ,

It seems like, in SQL, you have "\n" in the string. In ADF, you can add a DataFlow after the CopyActivity to do the transformation on the data. steps would be: in the CopyActivity, sink the file in a temp location. Now, in the DataFlow, add a DerivedColumn with replace() expression e.g., replace(Column, '\n', '') to replace new-line "\n" with empty string "" and sink the file in the Desire Location. If you want you can move/delete the source file from temp-location after sink completion (please see the screenshot).

Hope this helps. If this helps, please "accept the answer" and "up-vote"! Thank you!