How to capture exception logs when Azure Data Factory Pipeline fail

In this blog I am going to explain you how to store error logs using Stored procedure activity when Azure Data Factory Pipeline fail.

First you need to create a Exceptionlogs table using following SQL script.

CREATE TABLE [dbo].[ExceptionLogs](

[DataFactoryName] [varchar](100) NULL,

[PipelineName] [varchar](100) NULL,

[RunId] [varchar](100) NULL,

[ErrorMessage] [varchar](1000) NULL,

[CreatedOn] [datetime] NULL

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[ExceptionLogs] ADD  CONSTRAINT [DF_CreatedOn]  DEFAULT (getdate()) FOR [CreatedOn]

GO

Also create a store procedure suppose you are going to use in ADF Pipeline.

CREATE PROCEDURE [dbo].[Usp_ExceptionLog]

(@DataFactoryName varchar(100), @PipelineName varchar(100), @runid varchar(100),@ErrorMessage varchar(1000))

AS

BEGIN

INSERT INTO

ExceptionLogs

(

[DataFactoryName],

[PipelineName],

[RunId],

[ErrorMessage]

)

VALUES

(

@DataFactoryName,

@PipelineName,

@runid,

@ErrorMessage

)

END

GO

Here we are passing parameter like ADF name, Pipeline name, runid and error message that will mapped in ADF pipeline.

Suppose you already created pipeline with some activities in ADF:

Add a Stored Procedure activity to the canvas and connect another activity to this new activity using the arrow. When the connection has been made, right-click on the connection to change it to a Failure precedence constraint. This will change the color of the connector to red.

Now click on Stored procedure activity and set the details like Stored procedure name and set the stored procedure parameters as seen in the below screen.

Now we can run the pipeline and wait to see if failure of pipeline exception logs were stored in table.

I hope this will help you.