space charecter issue in sink

Gowri Shankar 226 Reputation points
2020-12-23T07:49:31.833+00:00

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.

50792-image.png

My sink setting is like below.
50772-image.png

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

Thanks

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,455 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Nasreen Akter 10,736 Reputation points
    2020-12-23T13:07:11.43+00:00

    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!

    50759-wordwrap.jpg


  2. Nasreen Akter 10,736 Reputation points
    2020-12-23T15:32:45.517+00:00

    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!

    50826-img1.jpg

    0 comments No comments