The issue with the on-fail activity

Eddy Tran 40

Dear Mr/Ms,

I created a pipeline, there is a If Condition Activity in the pipeline as below:
User's image

The content in the If Conditioin is as below: User's image

In If Condition, I set up the pipeline, if any activities failed, the next step is updating by Store procedure.

Get back to the first image, you can see, the activity Upsert tables Assets Hub is failed but there is no Store procedure running after the failure. I want to ask the reason why the store procedures updating failed status are not running in this case?

Many thanks!

Chandra Boorla 1,890 Reputation points Microsoft Vendor

2024-07-30T06:39:29.8533333+00:00
Hi @Eddy Tran

Thanks for the question and using MS Q&A platform.

As I understand that you have an Azure Data Factory pipeline with an "If Condition" activity designed to trigger a stored procedure if any upstream activities fail. However, despite the "Upsert tables Assets_Hub" activity failing, the expected stored procedure isn't executing.

There are several potential reasons why the store procedure is not running, including issues with the configuration of the If Condition activity, activity dependencies, error handling settings, or other factors.

To troubleshoot this issue, I recommend reviewing the configuration of the If Condition activity to ensure that the condition logic is correctly checking for the failure status of the "Upsert tables Assets Hub" activity.

Also, it's worth noting that the behavior of the pipeline depends on the error handling mechanism you have implemented.

Different error handling mechanisms lead to different status for the pipeline: while some pipelines fail, others succeed. We determine pipeline success and failures as follows:

Evaluate outcome for all leaves activities. If a leaf activity was skipped, we evaluate its parent activity instead

Pipeline result is success if and only if all nodes evaluated succeed

For more details, please refer: How pipeline failure are determined.

Additionally, verify that the store procedure activity is correctly linked as a dependent activity to the If Condition activity, and check for any specific error handling settings or retry policies that might be affecting the execution flow.

It may also be helpful to review the logs and debugging information for both the "Upsert tables Assets Hub" and the If Condition activities, as this can provide insights into any errors or issues that might be preventing the store procedure from running.

I hope this information helps. Please do let us know if you have any further queries.
Eddy Tran 40 Reputation points

2024-07-30T07:42:07.1933333+00:00

Hi Chandra Boorla,
As you can see in below picture, the last activity is Upsert tables Assets Hub, there is no other activities for fail path.

I just think, if the Upsert tables Assets Hub is failed, the fail path will be triggered, in this case, some activities of SP such as Update History, update Gold Layer must run but no activities for fail path runs.

I also checked the SP, if I run it seperately, it worked and data updated to database.

Do you have any other ideas about this issue?

Thanks!
Chandra Boorla 1,890 Reputation points Microsoft Vendor

2024-08-01T17:30:41.54+00:00

Hi @Eddy Tran

Thank you for providing additional details about the pipeline structure. Based on the information you have shared, it seems that the If Condition activity might not be triggering the expected failure path even though the "Upsert tables Assets Hub" activity has failed.

To effectively troubleshoot this issue, here are a few more ideas to explore:

Configuration of the If Condition Activity - Condition Logic: Ensure that the condition logic in the If Condition activity is correctly set to check for the failure status of the “Upsert tables Assets Hub” activity. Double-check the expressions used in the condition.

Activity Dependencies - Dependencies: Verify that the stored procedure activity is correctly linked as a dependent activity to the If Condition activity. Ensure that the failure path is properly configured to trigger the stored procedure.

Error Handling Settings - Error Handling: Review the error handling settings for the “Upsert tables Assets Hub” activity. Make sure that the activity is set to fail on error and that the failure path is correctly defined.

Retry Policies - Retry Policies: Check if there are any retry policies set for the “Upsert tables Assets Hub” activity. If retries are configured, the stored procedure might not trigger until all retries are exhausted.

Consider these additional steps that might help you:

Test the Stored Procedure: Since you mentioned that the stored procedure works when run separately, try creating a simple test pipeline with just the stored procedure to ensure it runs correctly within the pipeline context.

Simplify the Pipeline: Temporarily simplify the pipeline to isolate the issue. Remove other activities and focus on the If Condition and stored procedure to see if the issue persists.

If the issue still persists, please do let us know. Glad to help.

I hope this information helps.
Eddy Tran 40 Reputation points

2024-08-02T05:05:02.17+00:00

Hi Chandra Boorla,
I made the pipeline in the simple way.

I detected that the reason which makes 2 SP: Update History Table Fail Hub and Update History Table Fail Sat not running after Copy activity failed is the path 3 and path 4 in the second image. If I removed path 3 and path 4, in the first image, you can see Update History Table Fail Hub and Update History Table Fail Sat run after Copy activity failed.

The purpose of adding path 3 and path 4 is resuing the store procedures "Update History Table Fail Hub" and "Update History Table Fail Sat not running" when copy activity or store procedure Ussert Tables Assets Hub failed.

Can you explain why I can not use path 3 and path 4 with path 1 and path 2 simutaneously? Are there any other solutions for resuing SP?

Thanks!
Chandra Boorla 1,890 Reputation points Microsoft Vendor

2024-08-05T10:42:56.61+00:00
Hi @Eddy Tran

Once again thank you for providing the additional information about the pipeline structure.

Why Path 3 and Path 4 Cause Issues:

When you add Path 3 and Path 4, you are essentially creating multiple downstream dependencies for the same activities. This can lead to situations where the execution flow might get confused or not execute as expected due to conditional paths and dependencies. In Azure Data Factory (ADF) or other pipeline orchestration tools, when a downstream activity has multiple upstream dependencies, all upstream dependencies must complete before the downstream activity can execute. So, if you have paths 3 and 4, the conditions for executing "Update History Table Fail Hub" and "Update History Table Fail Sat" become complex and dependent on multiple upstream activities.

Example Scenario:

Consider a scenario where the Copy Activity fails:

Path 1 should trigger "Update History Table Fail Hub".

Path 2 should trigger "Update History Table Fail Sat".

Similarly, if the Stored Procedure Ussert Tables Assets Hub fails:

Path 3 should trigger "Update History Table Fail Hub".

Path 4 should trigger "Update History Table Fail Sat".

However, if both Path 1 and Path 3 are connected to "Update History Table Fail Hub", ADF may wait for both dependencies to be fulfilled, potentially leading to a deadlock or no execution if the conditions are not perfectly aligned.

Solutions for Reusing Stored Procedures: To achieve the reuse of stored procedures without encountering dependency issues, consider the following approaches:

Independent Failure Handling Activities: Create separate failure handling activities for each failure scenario, ensuring that each activity is only triggered by its respective failure path. Failover Mechanism: Implement a global error handler that invokes the appropriate stored procedure based on the failure reason.

Custom Activity or Webhook: Create a custom activity or use a webhook to execute the necessary stored procedure based on the failure condition, centralizing the error handling logic.

Conditional Execution with Parameters: Use parameters and conditional expressions to determine which stored procedure to call. For example, create a single failure handling activity that checks the type of failure and invokes the appropriate stored procedure accordingly.

Example: Conditional Execution:

Create a Failure Handling Stored Procedure Activity:

Add a parameter to this activity to accept the type of failure.

Use an If condition within the stored procedure to execute the appropriate failure handling logic.

Modify Paths to Include Failure Type:

Path 1 and Path 3 can pass a failure type parameter indicating "Copy Failure".

Path 2 and Path 4 can pass a failure type parameter indicating "Stored Procedure Failure".

Single Failure Handling Activity:

This activity reads the parameter and decides which stored procedure to execute based on the failure type.

By implementing one of these solutions, you can avoid the potential issues caused by multiple downstream dependencies and ensure a more robust and efficient pipeline execution.

I hope this information helps. Please do let us know if you have any further queries.
Eddy Tran 40 Reputation points

2024-08-05T14:21:50.44+00:00

Hi Chandra Boorla,

Many thanks for your answer! I understand the reason for the issue. I try to understand the solution examples but I still not. Can you help me make the examples clearer with images? I'm sorry for bothering but actually I'm still a new bee in ADF.

Many thanks!
Chandra Boorla 1,890 Reputation points Microsoft Vendor

2024-08-07T16:37:37.09+00:00

Hi @Eddy Tran

Just wanted to check whether your issue got resolved or still facing the same issue.

If the issue still persists, please do let us know. Glad to help. In case if you have any resolution, please do share that same with the community as it can be helpful to others.
Eddy Tran 40 Reputation points

2024-08-08T07:30:50.15+00:00
Hi Chandra Boorla,

You gave me the solution:
Create a Failure Handling Stored Procedure Activity:

Add a parameter to this activity to accept the type of failure.

Use an If condition within the stored procedure to execute the appropriate failure handling logic.

=> You mean I have to change the content of SP script?

Modify Paths to Include Failure Type:

Path 1 and Path 3 can pass a failure type parameter indicating "Copy Failure".

Path 2 and Path 4 can pass a failure type parameter indicating "Stored Procedure Failure".

=> Where do I set up the type parameter?

Single Failure Handling Activity:

This activity reads the parameter and decides which stored procedure to execute based on the failure type.

=> You mean I have to create the SP for each type of activity?

Actually I have still not understood the way to implement the solution. Can you make this more detail?

Thanks!
Viet Tran 50 Reputation points

2024-08-13T01:46:34.63+00:00

Hi Chandra Boorla,
Can you help for the questions in comments written in Aug 8 2024?

Thanks!
Chandra Boorla 1,890 Reputation points Microsoft Vendor

2024-08-19T12:32:35.3533333+00:00

Hi @Eddy Tran

Just checking in to see if the below answer provided by @ Amira Bedhiafi helped.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

1 answer

Amira Bedhiafi 24,556 Reputation points

2024-08-15T21:31:00.5133333+00:00
Based on what @Chandra Boorla provided, I will try to detail the solution.

I am marking this question as a duplicate.

You need to create an SP to handle different types of failures (, "Copy Failure" and "Stored Procedure Failure") where you add a parameter to accept the type of failure.

CREATE PROCEDURE HandleFailure @FailureType NVARCHAR(50) AS BEGIN IF @FailureType = 'Copy Failure' BEGIN -- Add your logic to handle Copy Failure PRINT 'Handling Copy Failure'; END ELSE IF @FailureType = 'Stored Procedure Failure' BEGIN -- Add your logic to handle Stored Procedure Failure PRINT 'Handling Stored Procedure Failure'; END ELSE BEGIN PRINT 'Unknown Failure Type'; END END

You have multiple failure paths (Path 1, Path 2, Path 3, and Path 4) in your ADF pipeline.

For each failure path, set up a parameter that indicates the type of failure.

For Path 1 and Path 3 (handling failures related to Copy activity), pass a failure type parameter indicating "Copy Failure".

For Path 2 and Path 4 (handling failures related to Stored Procedure activity), pass a failure type parameter indicating "Stored Procedure Failure".

In the pipeline designer, add a Set Variable or Execute Stored Procedure activity after the failure paths and pass a literal value to a parameter indicating the type of failure ("Copy Failure" or "Stored Procedure Failure").

{ "type": "ExecuteStoredProcedure", "typeProperties": { "storedProcedureName": "HandleFailure", "storedProcedureParameters": { "FailureType": { "value": "Copy Failure", "type": "String" } } } }

Instead of handling failures separately in multiple places, you can use a single Execute Stored Procedure activity to call your failure handling stored procedure where you read the failure type parameter passed from each failure path and decide which part of the stored procedure logic to execute.

After any activity that can fail (like a Copy activity or another stored procedure), use an If Condition or directly pass the failure type parameter to your failure handling stored procedure.

{ "name": "FailureHandlingPipeline", "activities": [ { "name": "CopyActivity", "type": "Copy" // Other Copy activity settings }, { "name": "ExecuteFailureHandler", "type": "ExecuteStoredProcedure", "dependsOn": [ { "activity": "CopyActivity", "dependencyConditions": ["Failed"] } ], "typeProperties": { "storedProcedureName": "HandleFailure", "storedProcedureParameters": { "FailureType": { "value": "Copy Failure", "type": "String" } } } } ] }
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

The issue with the on-fail activity

1 answer

Your answer