locked
ADFv2 - CR/LF in SQL Server Varchar Column Causing Row Breaks in Data Lake Store PSV File RRS feed

  • Question

  • I am creating my first pipeline in ADFv2 (our current solution is ADFv1). I am reading from SQL Server database tables on a Virtual Machine in our Azure cloud, and writing to PSV files in the Data Lake Store. Everything is working well except for columns in one table that contain carriage return and line feed embedded in string data, which caused additional lines in the output file. I have tried using the double quote character in the Sink -> Connection -> Advanced -> Quote Character, but that did not help. Any suggestions?
    Wednesday, March 21, 2018 8:41 PM

Answers

  • Could you write a stored procedure in your source db to strip out (or replace) the CRLF; and add a Stored Procedure Activity to your pipeline somewhere before your Copy Activity?

    An alternative would be to change your SqlSource to call a query that strips out those characters on the fly.  There's an example in the docs:

    https://docs.microsoft.com/en-us/azure/data-factory/connector-sql-server

    • Marked as answer by Polly Herr Thursday, April 5, 2018 1:14 PM
    Tuesday, April 3, 2018 8:52 AM