Lookup Different Source Query Based on Pipeline Input Param Value

libpekin 126 Reputation points
2024-09-05T21:53:27.6633333+00:00

Hello,

I have a pipeline with a parameter where I need to perform a Lookup on the source query based on the input and then process that data. I cannot use a Switch Activity as I am utilizing a ForEach Activity for further data processing. Is it possible to accomplish this?

Note: Due to performance bottleneck the requirement is to use a single pipeline instead of two separate pipelines.

Thanks!

User's image

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,566 questions
Accepted answer
  1. phemanth 10,240 Reputation points Microsoft Vendor
    2024-09-09T16:01:42.9433333+00:00

    @libpekin

    Thanks for reaching out to Microsoft Q&A.

    1. Define the Pipeline Parameter:
      • Create a pipeline parameter, e.g., SourceSystem, which will hold the value to determine the source query.
    2. Add a Lookup Activity:
      • Drag a Lookup activity onto the pipeline canvas.
      • In the Lookup activity settings, configure the source dataset.

    Shows the UI for a Lookup activity.

    Set the Source Query Dynamically:

    • In the Lookup activity, use an expression to set the source query based on the pipeline parameter. For example:JSON 
        {
    "source": {
      "type": "AzureSqlSource",
      "sqlReaderQuery": {
        "value": "@concat('SELECT * FROM ', pipeline().parameters.SourceSystem, 'Table')",
        "type": "Expression"
      }
    }
  }
    • This expression concatenates the parameter value with the table name to form the query.

    Use ForEach Activity:

    • Add a ForEach activity to process the data returned by the Lookup activity.
    • Configure the ForEach activity to iterate over the items returned by the Lookup. Shows the UI for a Filter activity.

    Additional Resources

    This approach allows you to dynamically set the source query based on the pipeline parameter and process the data within a single pipeline

    Hope this helps. Do let us know if you any further queries.

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Sina Salam 10,111 Reputation points
    2024-09-05T22:28:53.4966667+00:00

    Hello libpekin,

    Welcome to the Microsoft Q&A and thank you for posting your questions here.

    I understand that you would like to combine the Lookup and ForEach activities in Azure Data Factory.

    Yes, you can achieve this in a single pipeline by Add a Lookup Activity, Add a ForEach Activity, https://www.mssqltips.com/sqlservertip/7916/azure-data-factory-foreach-lookup-activities and configure the ForEach Activity, https://video2.skills-academy.com/en-us/azure/data-factory/control-flow-for-each-activity.

    The below are the similar answers use case in this community:

    https://video2.skills-academy.com/en-us/answers/questions/1525747/sequence-value-insertion-in-the-copy-activity

    https://video2.skills-academy.com/en-us/answers/questions/1776875/copydata-in-foreach

    https://video2.skills-academy.com/en-us/answers/questions/1115974/azure-data-factory-foreach-overwriting-records-ins

    https://video2.skills-academy.com/en-us/answers/questions/1163886/how-to-loop-(foreach)-thru-copy-data-and-execute-s

    I hope this is helpful! Do not hesitate to let me know if you have any other questions.

    Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.