Efficient Data Copying from SQL Server to Azure Data Lake Storage Gen2: Stage-2: Store Logs

To achieve logging of each copy activity from SQL Server to Azure Data Lake Storage Gen2 using Azure Data Factory (ADF), and storing the log details in a SQL database using a stored procedure, you’ll need to follow a structured approach. Here’s how you can set this up step-by-step:

Designing the Logging Table

First, create a table in your SQL database that will store the log details for each copy activity:

CREATE TABLE CopyLog (
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    TableName NVARCHAR(128),
    DestinationPath NVARCHAR(MAX),
    RowsCopied INT,
    IsSucceeded BIT,
    DurationInSeconds INT,
    StartTime DATETIME
);

Creating a Stored Procedure for Logging

Next, create a stored procedure that will insert log entries into the CopyLog table:

CREATE PROCEDURE InsertCopyLog
    @TableName NVARCHAR(128),
    @DestinationPath NVARCHAR(MAX),
    @RowsCopied INT,
    @IsSucceeded BIT,
    @DurationInSeconds INT,
    @StartTime DATETIME
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO CopyLog (TableName, DestinationPath, RowsCopied, IsSucceeded, DurationInSeconds, StartTime)
    VALUES (@TableName, @DestinationPath, @RowsCopied, @IsSucceeded, @DurationInSeconds, @StartTime);
END;

Implementing the Data Factory Pipeline

Now, integrate logging into your Azure Data Factory pipeline to capture the necessary information and invoke the stored procedure to log each copy activity:

On Success Configuration

On Failure Configuration

Parameter values in expression:

# Destination Path
@concat('landing5/',item().Destination_Path, '/',item().Table_Name,'/', item().File_Format)

# Copy Duration
@activity('Copy data1').output.copyDuration

# Rows Copied
@activity('Copy data1').output.rowsCopied

# Start Time
@activity('Copy data1').output.executionDetails[0].start

# Table_Name
@item().Table_Name

pipeline execution

You can refer my old story for rest configuration here. Here is complete pipeline code.

Summary

By following this approach, you can effectively log each copy activity from SQL Server to Azure Data Lake Storage Gen2 using Azure Data Factory. This setup ensures that you capture essential details such as table name, destination path, number of rows copied, success status, duration, start time, and end time in your SQL database for auditing, monitoring, and troubleshooting purposes. Adjust the pipeline configuration and stored procedure as per your specific logging requirements and business needs.