Update DB when whole Synapse pipeline fail

Question

Update DB when whole Synapse pipeline fail

Lotus88 176

Hi,

Is there a way to update the DB (I already have a stored procedure) when the whole pipeline fail instead of update DB from individual activity ?

Nandan Hegde 35,356 Reputation points MVP Volunteer Moderator

2025-05-20T02:55:12.9866667+00:00

Can you please provide more details on your ask here? Is your ask to update a log table in database at the end of pipeline irrespective of failure at any activities?
Lotus88 176 Reputation points

2025-05-20T03:03:37.5766667+00:00

I have a sequence or concurrent activities. If any of the activities fail, I want to update the log table in database. I have a stored procedure to update this log table. However I don't want to add this stored procedure in every activities fail path as it will make the pipeline look very messy. Thanks!
J N S S Kasyap 2,610 Reputation points Microsoft External Staff Moderator

2025-05-20T03:04:47.65+00:00
Hi @Lotus88, In addition @Nandan Hegde response please help with below answers we can provide the answers to your ask?

Are you using any child pipelines via the “Execute Pipeline” activity?

Do you want to update the DB only when the entire pipeline fails, or for individual activity failures too?

Where is your stored procedure located (Azure SQL, Synapse Dedicated SQL Pool, etc.)?

What specific information do you want to log in the DB (e.g., pipeline name, RunId, error message)?
Lotus88 176 Reputation points

2025-05-20T03:23:40.8933333+00:00
Hi @J N S S Kasyap

Here are my answers:

I have one main pipeline call "execute pipeline" activity. I have a few sequence activities follow by "if conditions" and inside "If" there are other activities and notebook.

I want to update DB when any individual activity fail. The whole pipeline will fail too.

Stored procedure is in Azure SQL DB.

Log Pipeline name, RunId, Error message of that fail activity
J N S S Kasyap 2,610 Reputation points Microsoft External Staff Moderator

2025-05-20T05:26:31.4366667+00:00

@Lotus88

You can update your database using a stored procedure when the entire Synapse pipeline fails. Since Synapse doesn’t have a native "on pipeline failure" trigger, you handle this by attaching failure paths from key activities to a centralized Stored Procedure activity.
For each critical activity (like notebooks or transformations), add an OnFailure dependency to the Stored Procedure activity. This ensures the stored procedure runs if any of these activities fail, which in turn will cause the whole pipeline to fail.
In the Stored Procedure activity, pass parameters like @pipeline().Pipeline, @pipeline().RunId, and the error message using @activity('ActivityName').Error.Message. This allows you to log the pipeline name, run ID, and error details to your Azure SQL DB.

This approach ensures that any failure in the pipeline results in your logging logic being triggered and your stored procedure updating the DB accordingly.

I hope this information helps. Please do let us know if you have any further queries.
Lotus88 176 Reputation points

2025-05-20T07:36:38.62+00:00

"... attaching failure paths from key activities to a centralized Stored Procedure activity." =>That is what I don't want to do as it make my pipeline look messy.
J N S S Kasyap 2,610 Reputation points Microsoft External Staff Moderator

2025-05-21T13:21:05.98+00:00

@Lotus88
In scenarios where you want to centralize error handling without cluttering the main pipeline, we recommends using a parent pipeline. This parent pipeline executes the child pipeline using the Execute Pipeline activity.

You can then add a Stored Procedure activity in the parent pipeline, connected via an OnFailure dependency from the Execute Pipeline activity. This setup ensures that if any activity within the child pipeline fails, the failure is propagated to the parent pipeline, triggering the stored procedure to log the error.

I hope this information helps. Please do let us know if you have any further queries.
J N S S Kasyap 2,610 Reputation points Microsoft External Staff Moderator

2025-05-22T09:14:18.9133333+00:00

@Lotus88
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Accepted answer

0 additional answers

Your answer

Nandan Hegde 35,356 Reputation points MVP Volunteer Moderator

2025-05-20T02:55:12.9866667+00:00

Can you please provide more details on your ask here? Is your ask to update a log table in database at the end of pipeline irrespective of failure at any activities?
Lotus88 176 Reputation points

2025-05-20T03:03:37.5766667+00:00

I have a sequence or concurrent activities. If any of the activities fail, I want to update the log table in database. I have a stored procedure to update this log table. However I don't want to add this stored procedure in every activities fail path as it will make the pipeline look very messy. Thanks!
J N S S Kasyap 2,610 Reputation points Microsoft External Staff Moderator

2025-05-20T03:04:47.65+00:00

Hi @Lotus88, In addition @Nandan Hegde response please help with below answers we can provide the answers to your ask?

Are you using any child pipelines via the “Execute Pipeline” activity?

Do you want to update the DB only when the entire pipeline fails, or for individual activity failures too?

Where is your stored procedure located (Azure SQL, Synapse Dedicated SQL Pool, etc.)?

What specific information do you want to log in the DB (e.g., pipeline name, RunId, error message)?
Lotus88 176 Reputation points

2025-05-20T03:23:40.8933333+00:00

Hi @J N S S Kasyap

Here are my answers:

I have one main pipeline call "execute pipeline" activity. I have a few sequence activities follow by "if conditions" and inside "If" there are other activities and notebook.

I want to update DB when any individual activity fail. The whole pipeline will fail too.

Stored procedure is in Azure SQL DB.

Log Pipeline name, RunId, Error message of that fail activity
J N S S Kasyap 2,610 Reputation points Microsoft External Staff Moderator

2025-05-20T05:26:31.4366667+00:00

@Lotus88

You can update your database using a stored procedure when the entire Synapse pipeline fails. Since Synapse doesn’t have a native "on pipeline failure" trigger, you handle this by attaching failure paths from key activities to a centralized Stored Procedure activity.
For each critical activity (like notebooks or transformations), add an OnFailure dependency to the Stored Procedure activity. This ensures the stored procedure runs if any of these activities fail, which in turn will cause the whole pipeline to fail.
In the Stored Procedure activity, pass parameters like @pipeline().Pipeline, @pipeline().RunId, and the error message using @activity('ActivityName').Error.Message. This allows you to log the pipeline name, run ID, and error details to your Azure SQL DB.

This approach ensures that any failure in the pipeline results in your logging logic being triggered and your stored procedure updating the DB accordingly.

I hope this information helps. Please do let us know if you have any further queries.
Lotus88 176 Reputation points

2025-05-20T07:36:38.62+00:00

"... attaching failure paths from key activities to a centralized Stored Procedure activity." =>That is what I don't want to do as it make my pipeline look messy.
J N S S Kasyap 2,610 Reputation points Microsoft External Staff Moderator

2025-05-21T13:21:05.98+00:00

@Lotus88
In scenarios where you want to centralize error handling without cluttering the main pipeline, we recommends using a parent pipeline. This parent pipeline executes the child pipeline using the Execute Pipeline activity.

You can then add a Stored Procedure activity in the parent pipeline, connected via an OnFailure dependency from the Execute Pipeline activity. This setup ensures that if any activity within the child pipeline fails, the failure is propagated to the parent pipeline, triggering the stored procedure to log the error.

I hope this information helps. Please do let us know if you have any further queries.
J N S S Kasyap 2,610 Reputation points Microsoft External Staff Moderator

2025-05-22T09:14:18.9133333+00:00

@Lotus88
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Answer 1

Please refer the below blog to avoid adding a SP activity at the end of each activity but you can use a combination of dependencies across each activities and compare whether there is any error and log accordingly.

https://datasharkx.wordpress.com/2021/08/19/error-logging-and-the-art-of-avoiding-redundant-activities-in-azure-data-factory/

That is how you can capture all error messages across all activities.

If you want to avoid that, you can call your pipeline from another pipeline and add that SP post the execute pipeline activity.

Share via

Update DB when whole Synapse pipeline fail

0 additional answers

Your answer