unable to get the value in the variable dynamically and need the value should pass in the copy activity sequentially

2023-06-01T12:12:04.74+00:00

Hi,

Scenario: table1 have ID values and table2 have other data but ID's are same. Using table1 ID's need to fetch the data from table2 based on ID filter and load to table3(target). That would be in sequentially.

ex: table1 we need to fetch only ID values that i get it from lookup activity.

select ID from table1;

table2 we have 5 records but 2 records are already loaded into table3(target). so i want to load it remaining data into sequentially based on ID

for that i used For-Each activity to load the data sequentially into table3.but when i used copy activity to load the data into table3. in copy activity the source I written the query like "select name,ID,program table2 where ID='@{variables('ID')}' and mapped to table3. but the variable is not fetching value. when created a variable in the pipeline i didn't pass any value in the variable value. when i run the pipeline its not fetching any records. Please let me know how can i get the values in the variable please tell me it will help me a lotimage

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,120 questions
{count} votes

Accepted answer
  1. QuantumCache 20,261 Reputation points
    2023-06-01T17:46:34.7133333+00:00

    Hello @Bommisetty, Rakesh (Hudson IT Consultant)

    In Azure Data Factory (ADF), you can use dynamic content to pass values between activities!!

    In your case, you want to pass the ID value from a Lookup activity to a ForEach activity, and then use that value in a Copy activity,
    It seems like you are trying to fetch data from table2 based on the ID values from table1 and load it into table3 sequentially using the For-Each activity, Right? Is the understanding correct?

    Step1: Use a Lookup Activity to Fetch all the Record IDs from Table1 and then Set a Variable inside ForEach Activity which will be further used by a Copy Activity in the ForEach as a Final step.
    Also create a GLobal variable called ID of type String!!!

    User's image

    Step 2: ForEach Activity, Choose the Sequential option to loop through all the IDs from Lookup activity!!!

    User's image

    Now set the variable , use the casting to take care of handling the Integer if needed!User's image

    Copy Activity:
    Source:
    Write a query like this:

    SELECT Name, ID, Program FROM table2 WHERE ID = @{variables('ID')}
    

    User's image

    SINK:
    Make sure to Run the Source Preview with Preview and do the Mapping!
    User's image

    Mapping
    User's image

    User's image

    If the response is helpful, please click "Accept Answer" and upvote it. So that we can close this thread.

    Please comment in the below section for further help in this matter! Happy to help!

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful