How to collect data from different sources dynamically using foreach loop activity

[RajuArge]

Hi Team,

I would like to collect data from different data sources and store data in dataset dynamically using Foreach activity.
Once I collect the data from different sources via Foreach activity, i would like to perform join or union based on client data and configurations(json).

Is it possible to create datasets dynamically?

[v-nuoc-msft · Accepted solution]

Hi @RajuArge 

You can dynamically create datasets and perform join or merge operations using ForEach activities. The general steps are as follows:

Create a ForEach activity that traverses an array containing information about the data sources.

Inside the ForEach loop, you can use activities such as Copy Data to extract data from each source and store it in a temporary dataset.

At the end of the ForEach loop, use the Data Flow activity to join the two datasets.

Store the joined dataset in the target location.

ForEach activity - Microsoft Fabric | Microsoft Learn

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RajuArge]

Hi Nono Chen,

Thank you so much for quick reply. API is also one of my data source so , how we pull data from API efficiently iteratively. Can you please help me out with this?

[v-nuoc-msft]

Hi @RajuArge 

If you have multiple APIs to deal with, you may want to consider the following suggestions:

Create a configuration file that stores all API information in a json file or other format. Example:

{
  "apis": [
    {
      "name": "API 1",
      "url": "https://api.example1.com/data",
      "method": "GET",
      "auth": "Bearer YOUR_API_KEY"
    },
    {
      "name": "API 2",
      "url": "https://api.example2.com/data",
      "method": "GET",
      "auth": "Bearer YOUR_API_KEY"
    }
  ]
}

Add a Foreach activity to iterate through the list of APIs. Transfer the list of APIs as a collection of inputs to the Foreach activity.

Add a Web activity inside Foreach to extract the URL of the current API with dynamic content, for example:

@{item().url}

Select the request method, GET.

@{item().method}

Store the response data for each API into a temporary dataset.

Data from multiple APIs can be merged in subsequent steps using the Data Flow activity.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.