Issue with Lookup activity in Data Factory

Question

I am facing issue while updating the Error message of a previous activity into a sql table using Look up activity . Below is the error message

Error:Failure happened on 'Source' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Incorrect syntax near 'Source'. Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Incorrect syntax near 'Source'. Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.,Source=.Net SqlClient Data Provider,SqlErrorNumber=102,Class=15,ErrorCode=-2146232060,State=1,Errors=[{Class=15,Number=102,State=1,Message=Incorrect syntax near 'Source'.,},{Class=15,Number=319,State=1,Message=Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.,},],'

I am Using the below expression to update the table with error message in lookup activity:

Update dbo.ADFPipelineLogs
set Status='Failed' ,
ErrorMessage='@{activity('1-Demo_data').Error.Message}'
Select getdate();

I am not using any CTE but error says so. Can anyone help

Answer 1

Hello @Aman Sharma ,

Welcome to the Microsoft Q&A platform.

I had done a quick repro at my end and was not able to repro the issue on a normal circumstance.

I did see that you are making use dynamic content. I did try using the dynamic content. I was not still provided with the error.

However, when the dynamic content contain single quote (') within the body.

Something like below :

I was provided with the error as yours.

If this is your case i.e. activity('1-Demo_data').Error.Message - has a string returned with single quotes (') in the output(dynamic content). You're likely to encounter the behavior.

The error is because - the Single Quote (') is used to denote string boundary and this is causing the error while execution. If you want to represent a single quote (') within the string - we are supposed denote using '' (two single quotes)

Since, quote(') was causing the issue, I replaced the quote(') in the given string with a null using the replace() function.

This is 'with2' ---> This is with  

Change at my end

Update [SalesLT].[Address]  
set StateProvince='@{replace(variables('Error'),'''','')}'  
Select getdate();  

Output :

The below would be snippet change at your end.

'@{replace(activity('1-Demo_data').Error.Message,'''','')}'  

Note: Replacing the single quote (') with blank - the above snippet removes all the occurrence of the single quotes (') in the string.

If you want to retain the Single Quote in the Message string, you could use the below snippet :

   '@{replace(activity('1-Demo_data').Error.Message,'''','''''')}'  

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators