How to create new table from Azure data factory in Azure SQL when copy succeeded or fail-CodePudding

I am doing copy activity in Azure data factory. I am copying csv file to azure sql db. I already setup all the things from linker service to data set. What I want is if copy activity succeeded or fail then I want to insert TableName, No. of rows copied and status (succeeded, fail) into Another table in Azure SQL. How can it be done.

CodePudding user response：

You can use parameterized StoredProcedure Activity. You can then assign the values to parameter dynamically in pipeline.

Example:

Table to store copy details.

Create table [dbo].[CopyDetails](
TableName nvarchar(max),
NoOfRows int,
CopyStatus nvarchar(max)
)

Stored Procedure in same database as being copied

CREATE PROCEDURE recordDetails @TableName nvarchar(max), @NoOfRows int, @CopyStatus nvarchar(max)
AS
Insert into [dbo].[CopyDetails] 
VALUES
(@TableName, @NoOfRows, @CopyStatus)
GO

Create Variables accordingly in Pipeline

Sample working pipeline

Set variableRowsCopied

@string(activity('Copy data1').output.rowsCopied)

Set variableStatus

@activity('Copy data1').output.executionDetails[0].status

Set StoredProcedure Activity

Use variable to set dynamic values to Stored procedure parameters

Example:

After successfully executing