Unable to check the dataflow output activity metrics - ADF

vidhya rajan 0
Viewed 49

I am trying to check if the dataflow has written any rows to the sink and capture the activity output. the update statement fails, if the activity doesn't write any rows to the sink , so as per MS docs I am trying the below expression in a lookup activity.

***DECLARE @Date DATETIME;

SET @Date = GETDATE();

DECLARE @ROWSAFFECT INT;

SET @ROWSAFFECT =  if(contains(@{activity('dataflow').output.runStatus}, 'sink'), '@{activity('dataflow').output.runStatus.metrics.sink.rowsWritten}','0');

update table [schema].[audit_table] 
SET LOAD_STATUS ='Success' 
,ROWS_AFFECTED = @ROWSAFFECT

select 1;***

But this fails with a parse error. Can someone please help me with this?

=A database operation failed with the following error: 'Parse error at line: 4, column: 217: Incorrect syntax near ']'.'

expression azure-data-factory
1 Answers

The query written with dynamic content to assign the value for @ROWAFFECT is incorrect.

  • The main issue is with the @contains() inside the if. You should be searching for 'sink' in contains(activity('dataflow').output.runStatus.metrics.
  • I have tried the following query to insert into my azure SQL database table (dbo.audit) for demonstration.
DECLARE @ROWSAFFECT INT;

SET @ROWSAFFECT = @{if(contains(activity('dataflow').output.runStatus.metrics, 'sink'), activity('dataflow').output.runStatus.metrics.sink.rowsWritten,0)};

update [dbo].[audit] SET LOAD_STATUS ='Success' ,ROWS_AFFECTED = @ROWSAFFECT

enter image description here


The following is the debug query that is being run for each case.

  • When rowsWritten is 0:

enter image description here

  • When there are n=10 records:

enter image description here

Saideep Arikontham0
Related