Insert error from synapse data flow to SQL server DB

Question

Hi,

I am trying to capture an error throw by synapse data flow and insert to SQL DB table.

But I keep getting update error. I think the error contain single quote ', is there a way to insert the error to the table? Below is my code in Script activity:

insert into @{pipeline().parameters.logtable}

       (pl_run_id, error_log)

values ('@{pipeline().RunId}',

        '@{string(json(activity('DF Filter source with changed quotes').error.message).Message)}');

Answer 1

Hi @Lotus88
You’re absolutely right, trying to replace every special character manually can be a headache, especially when you don’t know all the characters that might cause issues.

A better way to handle this is by using a stored procedure in SQL. Instead of inserting the error message directly in your query (which can break because of special characters), you can send it as a parameter, and SQL will take care of the rest.

Here’s a better approach:

• Modify your Script Activity to call a stored procedure instead of directly inserting the error log.
• Create a stored procedure in SQL Server like this:

CREATE PROCEDURE InsertErrorLog  
    @pl_run_id NVARCHAR(100),  
    @error_log NVARCHAR(MAX)  
AS  
BEGIN  
    INSERT INTO YourLogTable (pl_run_id, error_log)  
    VALUES (@pl_run_id, @error_log);   
END

• Call the stored procedure from Synapse using the Execute activity or Lookup activity in ADF.

This way, SQL Server will handle escaping for you, and you won’t have to worry about special characters breaking the query.

I hope this helps. Please let me know if you need further information.