how to capture the each activity logs in the table in adf

Question

i want to capture every activity(lookup,execute pipeline,script,for each etc..) logs like success/failure and activity name into the table using adf

Answer 1

ADF provides built-in logging and monitoring features. Ensure that you have enabled diagnostic settings in ADF to capture detailed logs. This can be done through the Azure portal by navigating to your ADF instance and configuring the diagnostic settings to send logs to an Azure Storage Account, Log Analytics workspace, or Event Hub.

Set up a database (for example Azure SQL Database) with a table designed to capture activity logs. For example:

CREATE TABLE ADFActivityLogs (

    RunId NVARCHAR(50),

    PipelineName NVARCHAR(100),

    ActivityName NVARCHAR(100),

    ActivityType NVARCHAR(50),

    Status NVARCHAR(50),

    StartTime DATETIME,

    EndTime DATETIME,

    DurationInMs INT,

    ErrorMessage NVARCHAR(MAX)

)

• In each ADF pipeline, after each activity (e.g., Lookup, Execute Pipeline, Script, ForEach), you can add a Web Activity or Stored Procedure Activity to log the details into your custom table.
• Using Web Activity: If you're logging to an API or using Logic Apps to log to your database.
  • Example payload for a Web Activity:

    
           {
    
               "RunId": "@pipeline().RunId",
    
               "PipelineName": "@pipeline().Pipeline",
    
               "ActivityName": "LookupActivity",
    
               "ActivityType": "Lookup",
    
               "Status": "Success",  // Use conditional logic to determine success or failure
    
               "StartTime": "@activity('LookupActivity').start",
    
               "EndTime": "@activity('LookupActivity').end",
    
               "DurationInMs": "@activity('LookupActivity').duration",
    
               "ErrorMessage": "@activity('LookupActivity').error.message"
    
           }
    
    

• Using Stored Procedure Activity: If you're logging directly into an Azure SQL Database.
  • First, create a stored procedure in your database:

    
           CREATE PROCEDURE LogADFActivity
    
           @RunId NVARCHAR(50),
    
           @PipelineName NVARCHAR(100),
    
           @ActivityName NVARCHAR(100),
    
           @ActivityType NVARCHAR(50),
    
           @Status NVARCHAR(50),
    
           @StartTime DATETIME,
    
           @EndTime DATETIME,
    
           @DurationInMs INT,
    
           @ErrorMessage NVARCHAR(MAX)
    
           AS
    
           BEGIN
    
               INSERT INTO ADFActivityLogs 
    
               (RunId, PipelineName, ActivityName, ActivityType, Status, StartTime, EndTime, DurationInMs, ErrorMessage)
    
               VALUES 
    
               (@RunId, @PipelineName, @ActivityName, @ActivityType, @Status, @StartTime, @EndTime, @DurationInMs, @ErrorMessage)
    
           END
    
    

  • Then, in the Stored Procedure Activity, map the parameters from your ADF pipeline activities to the stored procedure parameters.
• For success/failure conditions, use an If Condition activity to determine the outcome of the previous activity and log the corresponding success or failure status.

Answer 2

Hi Bommisetty, Rakesh (Hudson IT Consultant) ,

In addition to the above approach, ou can log all the Pipeline runs,trigger runs,Activity runs segregated fashion USING "Azure Monitor" all at one go .

All the info will be logged in your storage account in form of Json format under today's date file in the format: yyyy-->Mm-->dd-->hh-->mm-->ss

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou