I want to have a ADF job that triggers on a SQL (Azure SQL DB) table re-load. I know that there is a Change Data Capture (CDC) trigger that can be used in ADF, but that seems to be something I would use for incremental changes. I need to trigger off a complete refresh of a table, know when that refresh is complete, then execute a SQL block. This data is coming from a Dynamics DMF job. We have tried using incremental updates with the DMF jobs, but have had bad results, so we do a flush and fill.

I want to have a ADF job that triggers on a SQL (Azure SQL DB) table re-load. I know that there is a Change Data Capture (CDC) trigger that can be used in ADF, but that seems to be something I would use for incremental changes. I need to trigger off a complete refresh of a table, know when that refresh is complete, then execute a SQL block. This data is coming from a Dynamics DMF job. We have tried using incremental updates with the DMF jobs, but have had bad results, so we do a flush and fill.

• sql
• triggers
• azure-data-factory
• change-data-capture

1 Answer 1

As per this Blog by @Davide Mauri,

In Azure SQL Database, you can call REST endpoint using sp_invoke_external_rest_endpoint in SQL trigger on your table.

You can try calling Function app or logic app with the REST API and then calling ADF from that. Or you can directly use logic app itself which had the triggers for the new SQL table item inserted or deleted as mentioned in this SO answer.

But these approaches trigger the ADF pipeline for every row change in the Azure SQL table. It means, a new pipeline run will be created for every update in the table.

I need to trigger off a complete refresh of a table, know when that refresh is complete, then execute a SQL block.

To trigger the Pipeline after complete refresh of the table, you can try the below workaround where it uses ADF pipeline scheduled triggers.

First analyze the data dump time of yours and give the same interval in the scheduled trigger of ADF.

In the SQL, create a table named events with a datetime column and update an old date in that. On every process of your table, the datetime in this table will be updated to new time and it will be used to check whether your table last updated time is greater than this or not.

Follow the below pipeline design in ADF.

- Lookup1 activity - take table events and get the maximum datetime.
- Lookup2 activity - take your table with below query.

SELECT OBJECT_NAME(OBJECT_ID) as tableName, 
       last_user_update AT TIME ZONE 'UTC' AT TIME ZONE 'India Standard Time' as lastUpdate
FROM sys.dm_db_index_usage_stats where OBJECT_NAME(OBJECT_ID)='<your_table_name>';

- If activity - Check whether the last modified datetime of your table from Lookup2 is greater than or eqauls to the maximum datetime from Lookup1.
    - True activities
        - Script activity - Insert a current datetime into the events table
        - Your activity - It might be the execution of your script or an Execute pipeline activity to execute your pipeline.
    - False activities - Leave this as empty.

You can do the same with a logic app as well. In this pipeline design, on every pipeline trigger run, if your table last modified time is not greater than the maximum datetime of the events table, then the pipeline won't execute your scripts.