ADF have built in Template Delta_CopyfromDB using control table ( see attached screen shot) This template is built in for one table delta load at a time , is there way to use this for multiple tables in DB also Parameters at bottom need to hard coded at run time , is there a way to make them dynamic so they can pick up changing directory every month like Data_Destination_Directory.

Hi @AzeemK , That is an interesting question. There are some options, I was thinking about: option#1: create an additional SQL table and save all the information there e.g., data_source_table_name, data_source_water_mark_column,..., valid_from_date, valid_to_date etc. Then for each row (i.e., each SQL table information) in the validTimePeriod (i.e., determine from the valid_from_date and valid_to_date), perform the DeltaLoadCopyActivity. option#2: for each table, schedule the pipeline at different time with hard-coded parameters. In this case, you might have to update the parameters in each month manually, if the values cannot be calculated by the Expression/function in the trigger. ---------- Please let me know if this helps. If it does, please don't forget to " accept the answer " and " up-vote "! Thank you!

Do incremental(delta) loads for multiple tables

Accepted answer

Nasreen Akter 10,751 Reputation points

2020-12-15T02:28:54.73+00:00

Hi @AzeemK ,

That is an interesting question.

There are some options, I was thinking about:

option#1: create an additional SQL table and save all the information there e.g., data_source_table_name, data_source_water_mark_column,..., valid_from_date, valid_to_date etc. Then for each row (i.e., each SQL table information) in the validTimePeriod (i.e., determine from the valid_from_date and valid_to_date), perform the DeltaLoadCopyActivity.

option#2: for each table, schedule the pipeline at different time with hard-coded parameters. In this case, you might have to update the parameters in each month manually, if the values cannot be calculated by the Expression/function in the trigger.

----------

Please let me know if this helps. If it does, please don't forget to "accept the answer" and "up-vote"! Thank you!
Please sign in to rate this answer.

3 people found this answer helpful.
AzeemK 516 Reputation points

2020-12-16T19:15:47.927+00:00

This doesn't help me much , if you have to delta load for 30 tables , I can't do 30 pipelines , there will be no valid to to Valid Thru date either,
Tables have just one delta columns called modified date , these is very common occurrence in ETL jobs , I am surprised ADF doesn't have a better solution than hardcoded parameters for single table

HarithaMaddi-MSFT 10,131 Reputation points

2020-12-17T13:08:44.797+00:00

Hi @AzeemK ,

Option 1 suggested by @Nasreen Akter does not need creating multiple pipelines but would need a configuration table to store all this information which will be used to loop through the same pipeline by passing these parameters. You would be using foreach activity for looping and lookup activity to retrieve table name value(looping id is passed as filter) that can be passed as parameter to the stored procedure that updates the watermark table for corresponding table row.

Please suggest for further challenges and we will be glad to assist. Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members.

HarithaMaddi-MSFT 10,131 Reputation points

2020-12-22T12:21:14.3+00:00

Hi @AzeemK ,

We have not received a response from you. Please suggest if above suggested approach is helpful. Otherwise, let us know and we will continue to engage with you on the query.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members.

HarithaMaddi-MSFT 10,131 Reputation points

2020-12-24T07:42:00.38+00:00

Hi @AzeemK ,

We still have not heard back from you. Following up to check if above suggested approach is helpful. Otherwise, let us know and we will continue to engage with you on the query.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members.
Sign in to comment

Do incremental(delta) loads for multiple tables

0 additional answers