Lookup Different Source Query Based on Pipeline Input Param Value

Question

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!

Answer 1

@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.

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.

Additional Resources

• Azure Data Factory Lookup Activity
• Azure Data Factory ForEach Activity

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.

Answer 2

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://learn.microsoft.com/en-us/azure/data-factory/control-flow-for-each-activity.

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

https://learn.microsoft.com/en-us/answers/questions/1525747/sequence-value-insertion-in-the-copy-activity

https://learn.microsoft.com/en-us/answers/questions/1776875/copydata-in-foreach

https://learn.microsoft.com/en-us/answers/questions/1115974/azure-data-factory-foreach-overwriting-records-ins

https://learn.microsoft.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