How to conditionate a Copy Activity without using If Condition Activity

Question

Hello,

I'm using a Copy Activity to run a periodic copy for Staging data from SQL DB to Parquet files.

It is a incremental load, so I'm comparing the Old delta value and the New delta value.

The Copy Activity is already nested inside a If Condition activity. When the Condition is TRUE, I do a Full Load. When it's False, I do the Delta Load I'm talking about.

The Copy Activity uses a SQL Query to select the data that has to be copied. I conditioned the Query so if both Deltas are equal, the query returns no result (SET NOCOUNT ON;). Otherwise, the query runs normally, selecting the data between the two Datetimes of the Delta values.

@if(equals(activity('LookupOldDeltaValue').output.firstRow.OldDeltaValue,activity('LookupNewDeltaValue').output.firstRow.NewDeltaValue),
    'SET NOCOUNT ON;',
    concat(activity('SQLSelectStatement').output.resultSets[0].rows[0].Query,
            ' WHERE ',
            activity('FilterNonActiveTables').output.value[0].DELTA_COLUMN,
            ' > ''',
            activity('LookupOldDeltaValue').output.firstRow[''],
            ''' AND ',
            activity('FilterNonActiveTables').output.value[0].DELTA_COLUMN,
            ' <=  ''',
            activity('LookupNewDeltaValue').output.firstRow.NewDeltaValue, ''''))

When both Deltas are equal, I receive the following error:

Failure happened on 'Source' side. ErrorCode=SqlInvalidDbQueryString,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The specified SQL Query is not valid. It could be caused by that the query doesn't return any data. Invalid query: 'SET NOCOUNT ON;',Source=Microsoft.DataTransfer.ClientLibrary,'

The problem is that if I make the SQL Query select any value, a new Parquet file is created in the Staging Area, with no data, so it is a useless file.

How can I conditionate my copy activity to not copy anything if it's already nested inside a If Condition Activity? I would like to avoid creating another pipeline too.

I appreciate the help, thanks

Answer 1

Hi, Basically, if there is no data yo do not copy. Else copy is it.

you can do one thing, you can use a set variable activity and use the below expression. Basically, trying to set a variable with the value : SET NOCOUNT ON or the actual query.

@if(equals(activity('LookupOldDeltaValue').output.firstRow.OldDeltaValue,activity('LookupNewDeltaValue').output.firstRow.NewDeltaValue),
    'SET NOCOUNT ON;',
    concat(activity('SQLSelectStatement').output.resultSets[0].rows[0].Query,
            ' WHERE ',
            activity('FilterNonActiveTables').output.value[0].DELTA_COLUMN,
            ' > ''',
            activity('LookupOldDeltaValue').output.firstRow[''],
            ''' AND ',
            activity('FilterNonActiveTables').output.value[0].DELTA_COLUMN,
            ' <=  ''',
            activity('LookupNewDeltaValue').output.firstRow.NewDeltaValue, ''''))

Next, use an if condition and decide if the variable contains proper query or just set no count on.

@equals(variables('variablename'),'Set No Count On')

In the true part, leave it empty. Meaning, if the variable is bringing Set no count on, we are not going to do anything.

In the false part of the if condition, add a copy activity and use the variable in the source of copy activity.

Hope it works.

Please let us know how it goes.