Remove CR and LF in Pipeline Data Factory expressions

Vivek Komarla Bhaskar 936 Reputation points
2022-12-01T12:48:54.37+00:00

266108-screenshot-2022-12-01-at-122438.png

1- I have pipeline which starts execution with a lookup activity running a stored procedure on synapse dedicated pool, and this execution returns me with a user-defined return when successful and when it fails it returns the error too.

2- When it fails:
I'm capturing the error into a variable named - ErrorMessage and below is the expression I'm using -

@replace(replace(replace(replace(string(replace(replace(replace(substring(substring(activity('EXEC spAsRunTVA').error?.message,indexOf(activity('EXEC spAsRunTVA').error?.message,'Errors=[{'),sub(length(activity('EXEC spAsRunTVA').error?.message),indexOf(activity('EXEC spAsRunTVA').error?.message,'Errors=[{'))),lastIndexOf(substring(activity('EXEC spAsRunTVA').error?.message,indexOf(activity('EXEC spAsRunTVA').error?.message,'Errors=[{'),sub(length(activity('EXEC spAsRunTVA').error?.message),indexOf(activity('EXEC spAsRunTVA').error?.message,'Errors=[{'))),'Message='),sub(length(substring(activity('EXEC spAsRunTVA').error?.message,indexOf(activity('EXEC spAsRunTVA').error?.message,'Errors=[{'),sub(length(activity('EXEC spAsRunTVA').error?.message),indexOf(activity('EXEC spAsRunTVA').error?.message,'Errors=[{')))),lastIndexOf(substring(activity('EXEC spAsRunTVA').error?.message,indexOf(activity('EXEC spAsRunTVA').error?.message,'Errors=[{'),sub(length(activity('EXEC spAsRunTVA').error?.message),indexOf(activity('EXEC spAsRunTVA').error?.message,'Errors=[{'))),'Message='))),',',''),'}',''),']','')),pipeline().globalParameters.Quote,''),'Message=',''),pipeline().globalParameters.Quote,'"'),'\','/')

The reason for me to use such a long expression is to filter only the relevant message. So as an example, I was trying to insert some null values into a table which don't allow nulls and during this execution the stored procedure fails and it returns the error below -

Failure happened on 'Source' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Cannot insert the value NULL into column 'TXStartDateTime', table 'Distribution_51.dbo.Table_7bb10d4562cd4e9e9b4c19fdf30f0a57_51'; column does not allow nulls. UPDATE fails.
The statement has been terminated.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Cannot insert the value NULL into column 'TXStartDateTime', table 'Distribution_51.dbo.Table_7bb10d4562cd4e9e9b4c19fdf30f0a57_51'; column does not allow nulls. UPDATE fails.
The statement has been terminated.,Source=.Net SqlClient Data Provider,SqlErrorNumber=50000,Class=16,ErrorCode=-2146232060,State=2,Errors=[{Class=16,Number=50000,State=2,Message=Cannot insert the value NULL into column 'TXStartDateTime', table 'Distribution_51.dbo.Table_7bb10d4562cd4e9e9b4c19fdf30f0a57_51'; column does not allow nulls. UPDATE fails.
The statement has been terminated.,},],'

Now using the expression above, I was able to limit the error message to below -
Cannot insert the value NULL into column TXStartDateTime table Distribution_51.dbo.Table_7bb10d4562cd4e9e9b4c19fdf30f0a57_51; column does not allow nulls. UPDATE fails.\r\nThe statement has been terminated.

All good until here.

3- I'm passing this ErrorMessage as a parameter input to the Dataflow, this step updates my log table with the relevant error message. The ETL is failing here for - Job failed due to reason: java.lang.NullPointerException

I'm sure it is because of the CR & LF characters in the error message. See above. I tried to look in the community for this error message and have tried all the suggested options like below -

regexReplace(regexReplace($ErrorMessage,[\n],''),[\r],'')
replace(replace($ErrorMessage,\n,''),\r,'')
replace($ErrorMessage,\r\n,'')

None of the above solutions work and I'm unable to progress from here. Your help here would be appreaciated

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
11,199 questions
{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.