Insert error from synapse data flow to SQL server DB

Lotus88 66

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)}');

User's image

Smaran Thoomu 20,305 Reputation points Microsoft Vendor

2025-02-19T07:47:49.5133333+00:00
Hi @Lotus88

Welcome to Microsoft Q&A platform and thanks for posting your query here.

The issue likely comes from single quotes (') in the error message, which can break the SQL INSERT statement. To fix this, you need to properly escape single quotes in the error message before inserting it into SQL Server.

Escape Single Quotes in the Error Message:

Modify your INSERT statement like this

INSERT INTO @{pipeline().parameters.logtable} (pl_run_id, error_log) VALUES ('@{pipeline().RunId}', '@{replace(string(json(activity('DF Filter source with changed quotes').error.message).Message), '''', '''''')}');

The replace() function escapes single quotes by replacing each ' with '' (which is the correct way to handle single quotes in SQL).

This ensures that the error message does not break the query when inserting into SQL Server.

I hope this helps. Please let me know if you need further information.
Lotus88 66 Reputation points

2025-02-20T06:53:57.8066667+00:00

Hi Thoomu,

Thank you for your reply.

The problem is there might be other key chars that might break the sql insert statement so if there are more than one beside single quotes, then it is difficult to replace one by one as we don't know how many chars is impacted.

Share via

Insert error from synapse data flow to SQL server DB

Your answer