question

NageshCL-5231 avatar image
0 Votes"
NageshCL-5231 asked ShaikMaheer-MSFT commented

Dynamic source query in ADF

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
image.png (8.6 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

ShaikMaheer-MSFT avatar image
0 Votes"
ShaikMaheer-MSFT answered ShaikMaheer-MSFT commented

Hi @NageshCL-5231 ,

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.


lookupactivity.gif (1.6 MiB)
image.png (98.0 KiB)
image.png (85.3 KiB)
switchactivity.gif (1020.1 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @NageshCL-5231 ,

Thank you for marking it as accepted answer.

0 Votes 0 ·