locked
Exception handling in ADF RRS feed

  • Question

  • I created a pipline cotaining the component copy data under move&transform. In the copy data component, the source dataset is a csv in azure blob storage, the sink is a Azure SQL database using stored procedure. When there's nothing wrong in the copy data process, everything is fine, I mean the pipline runs without any issue. However, when I try to make some problem on purpose like changing dates to unknown formats to raise some error when copying data to simulate the cases that might happen in future, so that we develop our stored procedure with TRY/CATCH blocks to handing those issues. The weird thing is, my stored procedure runs fine and can hanlde the errors properly, the pipline gives me error either like 

    "Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements......."

    or

    "The current transaction cannot be committed and cannot support operations that write to the log file......"

    my stored procedure is as below

    ALTER PROC [stage].[Loadxxxx8] 
    
    @passing [stage].[ods_xxxxx08] READONLY, --a table type
    @sourceFileName NVARCHAR(200),
    @batchSize INT
    AS
    BEGIN
    
    INSERT INTO  TRANTABLE
    SELECT @@TRANCOUNT 
    
    IF(@batchSize=0)
    	SET @batchSize = 10000
     
    SELECT 
    	ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS [row_no],
    	[gross_profit_rate]  ,
    	[billing_date]  ,
    	[sales_order_number]  ,
    	[sales_item_number]  ,
    	[so_creation_date]  
    INTO #tempTable FROM @passing
    
    CREATE CLUSTERED INDEX PK_On_TempTbl  ON #tempTable(row_no)
    
    
    DECLARE @LoopCounter INT = 1
    DECLARE @LoopTimes INT
    DECLARE @RowCnt INT 
    DECLARE @TotalRows INT
    
    SELECT @TotalRows = MAX(row_no) FROM #tempTable 
    SELECT @LoopTimes=CEILING(@TotalRows*1.0/@batchSize)
    
    
    INSERT INTO stage.ETL_log(sourceName  , err_msg  ) SELECT CONCAT(@sourceFileName,'data loading started'),  ERROR_MESSAGE ( ) 
    
    WHILE(@LoopCounter <= @LoopTimes)
    	BEGIN
    		
    		BEGIN TRY		 
    			
    INSERT INTO  TRANTABLE
    SELECT @@TRANCOUNT 
    			BEGIN TRANSACTION 
    				INSERT INTO [dbo].[ods_xxxxx8]
    				(
    						 
    						[gross_profit_rate]  
    						[billing_date]  ,
    						[sales_order_number]  ,
    						[sales_item_number]  ,
    						[so_creation_date]  			
    				)		
    				SELECT TOP(@batchSize) 						 
    						[billing_date]  ,
    						[sales_order_number]  ,
    						[sales_item_number]  ,
    						[so_creation_date]  
    				FROM #tempTable WHERE row_no >  (@LoopCounter - 1)*@batchSize
    		
    		SELECT  @RowCnt = @@ROWCOUNT
    		
    		COMMIT
    		
    		SET @LoopCounter = @LoopCounter + 1
    		
    		IF(@RowCnt<>@batchSize )
    			BEGIN 			
    			INSERT INTO stage.ETL_log(sourceName  , err_msg  ) SELECT CONCAT(@sourceFileName,' data loading:', (@LoopCounter-1-1)*@batchSize + @RowCnt ,' rows loaded'),  ERROR_MESSAGE ( ) 
    			END
    		ELSE 
    			BEGIN			
    			
    			--INSERT INTO stage.ETL_log(sourceName  , err_msg  ) SELECT CONCAT(@sourceFileName,' data loading:', CASE WHEN @batchSize = @RowCnt THEN @LoopCounter*@batchSize ELSE (@LoopCounter-1)*@batchSize+@RowCnt END,' rows loaded'),  ERROR_MESSAGE ( ) 
    			INSERT INTO stage.ETL_log(sourceName  , err_msg  ) SELECT CONCAT(@sourceFileName,' data loading:',(@LoopCounter-1)*@batchSize,' rows loaded'),  ERROR_MESSAGE ( ) 
    			
    			END	
    		
    		
    		END TRY
    		
    		BEGIN CATCH
    			
    			IF(@@TRANCOUNT>0)
    			ROLLBACK	
    			
    			--log etl error
    			INSERT INTO stage.ETL_log(sourceName  , err_msg  ) SELECT @sourceFileName,  ERROR_MESSAGE ( ) 
    			
    			
    			--log err data rows
    			INSERT INTO [stage].[ods_xxxxxCxxxxxx_ERRLOG]
    						(	
    						[gross_profit_rate]  
    						[billing_date]  ,
    						[sales_order_number]  ,
    						[sales_item_number]  ,
    						[so_creation_date]  	
    						)
    						SELECT 
    							TOP(@batchSize) 
    							@sourceFileName,
    							 ERROR_MESSAGE ( )  ,
    						[gross_profit_rate]  
    						[billing_date]  ,
    						[sales_order_number]  ,
    						[sales_item_number]  ,
    						[so_creation_date]  					 
    							FROM #tempTable WHERE row_no >  (@LoopCounter - 1)*@batchSize		
    
    			
    			SET @LoopCounter = @LoopCounter + 1	
    		
    		END CATCH	 
    	
    	END 
    	
    	INSERT INTO stage.ETL_log(sourceName  , err_msg  ) SELECT CONCAT(@sourceFileName,' data loading ended'),  ERROR_MESSAGE ( ) 
    
    END
    GO

    meanwhile, the below sql executes well in SSMS, the error can be handled and I can those problematic rows in the error log table.

    declare @t [stage].[ods_XXXXX8]
    insert into @t select * from TESTTABLE
     
     
    exec [stage].[LoadCXXXX08]
    @t, 'file1',1000

    Is there anyone who experience the same issue?


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.




    • Edited by Eric__Zhang Wednesday, August 28, 2019 2:11 AM
    Wednesday, August 28, 2019 12:49 AM

All replies

  • Hi Eric,

    The problem lies with the TRY/CATCH block in your stored procedure. 

    If you have a TRY/CATCH block then the likely cause is that you are catching a transaction abort exception and continue. In the CATCH block you must always check the XACT_STATE() and handle appropriate aborted and uncommitable (doomed) transactions. If your caller starts a transaction and the calee hits, say, a deadlock (which aborted the transaction), how is the callee going to communicate to the caller that the transaction was aborted and it should not continue with 'business as usual'? The only feasible way is to re-raise an exception, forcing the caller to handle the situation. If you silently swallow an aborted transaction and the caller continues assuming is still in the original transaction, only mayhem can ensure (and the error you get is the way the engine tries to protect itself).

    I recommend you go over Exception handling and nested transactions which shows a pattern that can be used with nested transactions and exceptions.

    For more details, please refer this thread on Stack nbsp;https://stackoverflow.com/questions/21930156/transaction-count-after-execute-indicates-a-mismatching-number-of-begin-and-comm

    Hope this helps.

    Wednesday, August 28, 2019 9:57 AM
  • This issue weird. I think the stored procedure might be no issue, since it can be executed with the same parameters in SSMS without problem. I guess the ADF might wrap the stored procedure with more transactions or something what which leads the try/catch failure. Do you have any documentaion about how SP executed in ADF?

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 30, 2019 9:49 AM