SQL Replace in Data Factory Copy Data Activity RRS feed

  • Question

  • Several of my pipelines have Copy Data activities that use stored procedures to read from my Azure SQL databases and output data to a data lake. These stored procs use the SQL REPLACE() function to strip out unwanted characters and line breaks in free text data. When running them directly against the database, the replace works as intended. But when the pipelines run, the replacement doesn't happen at all. This is causing my data lake files to be formatted incorrectly.

    Has anyone else experienced discrepancies between manually running queries vs. running them via data factories?

    Thursday, June 13, 2019 5:33 PM

All replies

  • Hi there,

    In your case, ideally you should have a stored procedure activity in your pipeline to transform the data first before copying. You can chain a copy activity to this stored procedure to make sure that the data is transformed first and then copied. Something like the screenshot below :

    This way you would be able to use the output from the stored procedure in your copy activity too.

    Friday, June 14, 2019 9:11 AM