How to create Incremental Data load pipeline without creating control table/watermark table and stored procedure in SQL Server where the sink is ADLS GEN 2

Question

Hi Team,

We have a requirement to create incremental data copy pipeline in Data Factory for SQL Server as Source and ADLS Gen 2 as Sink.

However, we don't have flexibility to create control table to update watermarking date timestamp column using stored procedure or enable change tracking mechanism at source side. We do have lastmodified datetime stamp column in SQL Table.

Can anyone suggest how to implement incremental data load without adding/changing any object at source side however need alternative approach where we achieve the incremental data movement pipeline.

Thanks you in anticipation!!

Regards,
Mahammad Khan

Answer 1

Hello @ABDULLA, MAHAMMAD ,
Thanks for the ask and using Microsoft Q&A platform .
Since your source is SQL , you need basically query the SQL table with a starttime and endtime . Let me elaborate .

The intend is to use the query option in the copy table . We need to create a query like

Select * from sometable where lastmodified between startdatetime and enddatetime

The startdatetime and enddate time can set dynamically using addMinutes function .

https://learn.microsoft.com/en-us/azure/data-factory/control-flow-expression-language-functions#addMinutes

The difference in the startdatetime and endtime should be same as the frequency of the trigger .

Please do let me know how it goes .
Thanks
Himanshu

-------------------------------------------------------------------------------------------------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators