Remove CR and LF in Pipeline Data Factory expressions
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