Dynamic source query in ADF

Question

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: -

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

Answer 1

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.

Step2: Create 6 variables to hold LastUpdateTime values for all 6 tables.

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

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.

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.