Loading yearly data using for each ADF activity

Question

Hi,

I want to load data from one SQL DB to another SQL DB.
Rows are approx 20-30 M.
So we are trying to load the data using yearly timestamp where lookup activity will be used to get the list of years.
and then this input will be passed to for each activity where we have copy activity inside the loop.

Could anyone suggest how to pass year values form lookup activity output to for each activity to copy the data,

Any suggestions are appreciated.

Answer 1

Hello anonymous user ,
Thanks for the ask and using Microsoft Q&A platform .
This is what I will suggest ,

1. Use a lookup ( lets name it Get distinct year ) , to get all the distinct years , Since you have SQL as source . A simple query like

"Select ditinct year from some table " should work .

2. Use a foreach and use the output of the lookup eg
   @activity('Get distinct year ').output.value
   3. Inside the Lookup , add a copy activity in the source option use the query and create a dynamic expression .

@markus.bohland@hotmail.de ('select * from [dbo].[YourTableName] where year=' , item().year ) .

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

Answer 2

Hi @HimanshuSinha-msft Thank you for the steps. I followed the same but getting below pop up while debugging it. ![152040-image.png][1] [1]: /api/attachments/152040-image.png?platform=QnA