Dynamic source query in ADF

Nagesh CL 621 Reputation points
2021-09-06T17:29:41.89+00:00

Hi All,

I need to bring in incremental data from my azure sql source. Basically filter based on UpdateTime. So, i have created a log table as below: -

129646-image.png

I have used a lookup activity to get the LastUpdateTime. I have to set the LastUpdateTime of these 6 tables to 6 variables. How can i loop and set these variable values? I can pivot and bring the LastUpdateTime in row. But then I have to use 6 setvariable tasks?

My source query is a single query which joins all these tables and get only the rows that have modified date greater than LastModifiedDate.

Once these variables are set, I will have to use these variables in sourcequery.

Thanks

Regards,
Nagesh CL

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,906 questions
0 comments No comments
{count} votes

Accepted answer
  1. ShaikMaheer-MSFT 38,306 Reputation points Microsoft Employee
    2021-09-07T13:27:30.897+00:00

    Hi @Nagesh CL ,

    Thank you for posting query in Microsoft Q&A Platform.

    You can use Switch activity inside ForEach activity to set values for all 6 variables.

    Please check below detailed steps with the implementation.

    Step1: Lookup activity to get data from LogTable.

    129898-lookupactivity.gif

    Step2: Create 6 variables to hold LastUpdateTime values for all 6 tables.
    129951-image.png

    Step3: Foreach activity to loop each item of lookup activity output.
    expression used: @activity('Lookup LogTable').output.value
    129961-image.png

    Step4: Use Switch Activity inside for each activity and have case blocks created for all 6 tables and inside each case block use set variable activity for that particular table as below.

    129933-switchactivity.gif

    Hope this will help. Thank you.

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

    • Please accept an answer if correct. 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.

0 additional answers

Sort by: Most helpful