Loading yearly data using for each ADF activity

Meher, Shweta 1 Reputation point
2021-11-23T11:01:20.183+00:00

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.

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

2 answers

Sort by: Most helpful
  1. HimanshuSinha-msft 17,721 Reputation points Microsoft Employee
    2021-11-23T22:01:31.517+00:00

    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 .

    1. Use a foreach and use the output of the lookup eg
      @activity('Get distinct year ').output.value
      1. 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 130616-image.png or upvote 130671-image.png 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
    No comments

  2. Meher, Shweta 1 Reputation point
    2021-11-24T09:53:17.137+00:00

    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

    No comments