How to prevent Copy Activity exeuction when there is no records found at SQL Server source to copy the data into Azure Data Lake

Question

Hi All,

Good to all!!

We have requirement in ADF where we are performing Incremental data movement for multiple tables using SQL Server Change Tracking feature. However, as part of the requirement we are triggering this pipeline for every 1 hour but in most cased only very few tables are having change capture or else sometime for all the table there is no records to process from source side. So, In this case our main goal is to avoid copy activity execution if there no change set exist for that particular table at source and only copy data for having change capture at source for that respective table or else in some case if there is no change data available at source for all the table then for each table copy activity execution should be avoid as part of the pipeline.

As overall, this will help pricing cost benefit when no copy activity execution happens when there is no records to process from source.

Appreciate if some one can help to provide solution to meet the requirement.

Thank you in anticipation!!

Best Regards,
Mahammad Khan

Answer 1

Hello @Abdulla Mahammad Khan Dawood and welcome to Microsoft Q&A.

To only run the copy activity if data exists, we first need to know if the data exists. This can be accomplished by using a Lookup activity to get the number of rows in the changeset, either by a stored proc or by a query. After the Lookup activity, use an If activity conditioned on the results of the Lookup to run the Copy Activity.

Since you are doing multiple tables, there is another option. In your SQL create a stored procedure which returns the names of tables which have changes ready.
Use a Lookup activity to call this stored procedure and fetch those table names.
Then use a ForEach activity to iterate over each table name, passing it to the parameterized dataset in the Copy activity. This way only those tables with changes get copied.

Please let me know if this helped or if you want further clarification.