SQL try catch in activity has no effect

I am trying to build a dyamic pipeline for multiple customers.

My source is a Synapse Serverless view on top of a Azure DataLake. Sometimes a customer hast (currently) no data in some views. In this case the views throw an error.

Msg 13807, Level 16, State 1, Procedure omteam, Line 2 [Batch Start Line 0]
Content of directory on path ‘*/omteam/*.csv’ cannot be listed.
Msg 4413, Level 16, State 1, Line 569
Could not use view or function ‘dbo.omteam’ because of binding errors.

I created a sql try catch for such a case:

BEGIN TRY
DECLARE @sql nvarchar(4000) = ‘SELECT * from dbo.omteam where DataLakeModified_DateTime between ”1900-01-01T00:00:00” and ”2024-08-19 11:39:55.0000000”’
exec sp_executesql @sql
END TRY

BEGIN CATCH

The SQL Statement works now in SSMS. In ADF the try catch has no affect, the activity still fails.

ADF error:

Failure happened on ‘Source’ side. ‘Type=Microsoft.Data.SqlClient.SqlException,Message=Content of directory on path ‘*/omteam/*.csv’ cannot be listed.
Could not use view or function ‘dbo.omteam’ because of binding errors.,Source=Framework Microsoft SqlClient Data Provider,’

Which is the same error as before i built the try catch.

Any idea?

I am trying to build a dyamic pipeline for multiple customers.My source is a Synapse Serverless view on top of a Azure DataLake. Sometimes a customer hast (currently) no data in some views. In this case the views throw an error.Msg 13807, Level 16, State 1, Procedure omteam, Line 2 [Batch Start Line 0]
Content of directory on path ‘*/omteam/*.csv’ cannot be listed.
Msg 4413, Level 16, State 1, Line 569
Could not use view or function ‘dbo.omteam’ because of binding errors. I created a sql try catch for such a case: BEGIN TRY
DECLARE @sql nvarchar(4000) = ‘SELECT * from dbo.omteam where DataLakeModified_DateTime between ”1900-01-01T00:00:00” and ”2024-08-19 11:39:55.0000000”’
exec sp_executesql @sql
END TRY

BEGIN CATCH

IF(ERROR_NUMBER() = 13807)
SELECT ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_MESSAGE() AS ErrorMessage;
ELSE
THROW
END CATCHThe SQL Statement works now in SSMS. In ADF the try catch has no affect, the activity still fails. ADF error: Failure happened on ‘Source’ side. ‘Type=Microsoft.Data.SqlClient.SqlException,Message=Content of directory on path ‘*/omteam/*.csv’ cannot be listed.
Could not use view or function ‘dbo.omteam’ because of binding errors.,Source=Framework Microsoft SqlClient Data Provider,’Which is the same error as before i built the try catch.Any idea? Read More

Cart

Cart