How to iterate "Copy data" activity that points to different REST API endpoints

King Java 520

Hi Folks,

I am trying to find a better solution to not clone 50+ pipelines. Basically, I have about 50+ REST API endpoints: Example:

https://api.example.com/Provider-Info?providerUid=1

https://api.example.com/Provider-Info?providerUid=2

....

https://api.example.com/Provider-Info?providerUid=51

I have created a simple pipeline to test using "Copy data", and it is working: User's image

I have source as REST (linked service): User's image

I am trying to modify this pipeline so that it dynamically alternates providerUid (from static like I have now).

So, data gets pulled from REST API --> Azure SQL Server table.

I am trying to alternate providerUid each time and iterate so that data gets inserted into Azure SQL for 50+ times using one pipeline.

What is best architecture to make this work?

phemanth 11,280 Reputation points Microsoft Vendor

2024-02-09T08:56:29.8533333+00:00
@King Java

Thanks for reaching out to Microsoft Q&A.

There are several ways to achieve dynamic alternation of providerUid in your Azure Data Factory pipeline without cloning it 50+ times. Here are three options with varying complexity:

1. Parameterize the REST API endpoint:

Define a pipeline parameter named providerUid.

Modify the REST API source URL to dynamically include the parameter, e.g., https://api.example.com/Provider-Info?providerUid=@providerUid.

During pipeline execution, set the providerUid parameter value to different IDs for each run. This can be done manually or through external triggers like triggers or expressions.

2. Use a ForEach activity:

Create a "ForEach" activity in your pipeline.

Define an array containing all the providerUid values (1, 2, ..., 51).

Inside the ForEach loop, copy the REST API data source and modify the URL like in option 1, using the current item from the array as the providerUid value.

This automatically iterates through each ID and runs the copy operation independently.

3. Leverage Data Flow with dynamic mappings:

Create a Data Flow in your pipeline with a REST source and an Azure SQL Sink.

Use a "Derived Column" transformation to dynamically set the providerUid value based on an expression or function (e.g., using the item position within the ForEach loop).

Map the dynamic providerUid and other required fields to the destination table.

This option offers more flexibility for data manipulation before inserting into the table.

Additional considerations:

Error handling: Implement proper error handling for each option to handle potential failures during API calls or data insertion.

Batching: If you're concerned about performance, consider batching multiple providerUid values together in a single API call or ForEach loop iteration.

Security: If the providerUid values are sensitive, ensure they are stored securely and accessed properly within the pipeline.

The best architecture depends on your specific needs and complexity. Start with the simplest option (parameterization) and move to more advanced solutions like Data Flow if needed. Consider factors like code complexity, flexibility, and error handling when making your decision.

Hope this helps. Do let us know if you any further queries.
King Java 520 Reputation points

2024-02-09T16:59:19.8266667+00:00

@phemanth Thank you for your help and feedback. Which one most standard way or easy approach out of first two options?
phemanth 11,280 Reputation points Microsoft Vendor

2024-02-09T20:33:11.4766667+00:00
@King Java In most cases, the Parameterized Copy activity is the most standard and easy approach to dynamically alternate providerUid within your Azure Data Factory pipeline for the reasons below:

Simplicity:

It requires fewer activities and configurations compared to using a Lookup activity and ForEach activity.

You don't need additional external resources like a separate source for providerUid values.

Maintainability:

Managing a single parameter is easier than maintaining a separate lookup activity and its source.

Changes to the list of providerUid values only require updating the parameter, not modifying the pipeline structure.

Performance:

The parameterized copy activity might be slightly more efficient than using a ForEach activity, especially for a large number of iterations.

However, consider the following scenarios where using ForEach activity might be beneficial:

Independent execution of iterations: If iterations need to run independently (e.g., to handle potential failures differently), a ForEach activity with individual retries offer fine-grained control.

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

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

-
King Java 520 Reputation points

2024-02-09T21:57:52.3333333+00:00

@phemanth Do you happen to know any YouTube content that would be very similar case? I would like to mimic the process (to save time).
phemanth 11,280 Reputation points Microsoft Vendor

2024-02-10T08:09:10.8433333+00:00
@King Java

I found some YouTube tutorials that might help you:

For Option 1: Parameterizing the REST API endpoint:

“Copy Data From An HTTP Endpoint By Using Azure Data Factory - ADF Tutorial 2021” - This tutorial demonstrates how to fetch only required records from a REST API and write them to Azure SQL DB in Azure Data Factory.

For Option 2: ForEach activity:

“ForEach activity Detailed Demo in Azure Data Factory Azure Data Factory Tutorial 2021” - This video provides a detailed demonstration of the ForEach activity in Azure Data Factory.

“33. ForEach Activity in Azure Data Factory” - This video discusses the ForEach Activity in Azure Data Factory.
King Java 520 Reputation points

2024-02-11T01:43:25.16+00:00

@phemanth Thank you for your help again. I watched your Youtube links, but I don't think they use parameters. I am trying to use multiple end points of API. How do I use dynamic parameters? I Googled and found this YouTube , but it still does not show how to use more than one usage of parameter, so I am stuck. I guess I am still trying to use "REST" as linked service still, not "HTTP". What do you think on that? The YouTube that you mentioned uses "HTTP" and then it appears that I need to work with the result of JSON file.
phemanth 11,280 Reputation points Microsoft Vendor

2024-02-12T19:33:05.6533333+00:00

@King Java I couldn't found YouTube video, but I will provide some documents please go through it which might help you

https://stackoverflow.com/questions/47465759/passing-multiple-parameters-to-rest-api-with-multiple-conditions

https://learn.microsoft.com/en-us/azure/data-factory/how-to-expression-language-functions

https://techcommunity.microsoft.com/t5/fasttrack-for-azure/reuse-adf-entities-with-dynamic-parameters/ba-p/3302377
King Java 520 Reputation points

2024-02-12T20:59:42.8433333+00:00

@phemanth Thank you for your help. I think the third link is probably the most helpful for now. Let me try and let you know. If I still struggle, I will definitely let you know.
King Java 520 Reputation points

2024-02-13T00:45:50.7766667+00:00

@phemanth I am providing some screenshots on the bottom: I am still trying to figure out how to express Parameters on Lined Service area: I have URI something like this: https://api.example.com/FiveStar/Provider-Info?providerUid=6537 https://api.example.com/FiveStar/Provider-Info?providerUid=7944 I have set up Base URL like this: https://api.example.com/FiveStar/

When I ran the pipeline, it only inserted data of 7944. What am I missing?
phemanth 11,280 Reputation points Microsoft Vendor

2024-02-13T13:16:30.67+00:00
@King Java

Check if the relative URL is correctly constructed. Ensure it includes the providerUid parameter.

Verify that the API endpoint for 6537 exists and is accessible.

Debug any issues related to data retrieval or processing.
King Java 520 Reputation points

2024-02-13T19:32:34+00:00

@phemanth I think all three check points are there.

I just am not sure the way I constructed each areas (such as {'6537','7944'} or something like using dataset(). ) are correct.

I could use Relative URLs for each, but I still have to manually create 50+ pipelines.

Did you take a look at my screenshots by chance?

I feel like I am almost there, but I need some screenshots or some type of validation to know where I need to correct.

Thanks.
phemanth 11,280 Reputation points Microsoft Vendor

2024-02-14T06:26:09.9866667+00:00

@King Java could you provide provide more details about how your pipeline is set up? screen shot if possible of pipeline setup.
King Java 520 Reputation points

2024-02-14T18:03:49.17+00:00

@phemanth Thanks for helping on this issue: Appreciated.

This is error message:

ErrorCode=RestCallFailedWithClientError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Rest call failed with client error, status code 400 BadRequest, please check your activity settings. Request URL: https://api.XXX/v2/analytics/FiveStar/Provider-Info?providerUid=%7B'6537',%20'7944'%7D. Response: {"errors":{"providerUid":["The value '{'6537', '7944'}' is not valid.","A value for the 'providerUid' parameter or property was not provided."]},"type":"https://tools.ietf.org/html/rfc7231#section-6.5.1","title":"One or more validation errors occurred.","status":400,"traceId":"00-9233fbabf925accb7f766b4b55da069c-888d4cbdcf42e376-00"},Source=Microsoft.DataTransfer.ClientLibrary,' Here are some screenshots:

Parameter:
phemanth 11,280 Reputation points Microsoft Vendor

2024-02-15T09:39:35.9333333+00:00
@King Java

The error message you’re seeing is indicating a 400 BadRequest error. This typically means that the server could not understand the request due to invalid syntax. From the error message, it seems like the issue is with the providerUid parameter in your API request. The server is expecting a valid value for providerUid, but it’s receiving {'6537', '7944'} which it’s not able to process. Here are a few things you could check:

Format of the providerUid parameter: Ensure that the providerUid parameter is in the correct format that your API endpoint is expecting.

Value of the providerUid parameter: Check if {'6537', '7944'} is a valid value for providerUid. If not, you might need to provide a valid providerUid.

API Documentation: Refer to the API documentation for the correct usage of the providerUid parameter.

Please note that without the actual API documentation or access to the code, these are just educated guesses based on the error message. It’s always best to refer to the API documentation or reach out to the API provider for accurate information.
King Java 520 Reputation points

2024-02-15T19:57:11.51+00:00

@phemanth

Thank you for your help.

If I run two pipelines separately for both ProviderUid 6537 and 7944, there is no issue.

I just tested two separate pipelines and I got the data inside Azure SQL. So, it is not providerUid issue.

I need some guideline in which part of my set up is wrong.

Based on the error message, it does not take each ProviderUid separately: https://api.simpleltc.com/v2/analytics/FiveStar/Provider-Info?providerUid=%7B'6537',%20'7944'%7D. Response: {"errors":{"providerUid":["The value '{'6537', '7944'}' is not valid.","A value for the 'providerUid' parameter or property was not provided."]},

I think the issue is how to iterate value of providerUid(). Is using parameter the right approach to iterate or should I use array?
King Java 520 Reputation points

2024-02-16T02:11:32.3433333+00:00

@phemanth I decide to use For Each instead of Parameter: Here are screenshot of set up. I am not getting two of same insert. Can you please take a look and see what is wrong?

Inside ForLoop ---> CopyData:

Result:

Accepted answer

phemanth 11,280 Reputation points Microsoft Vendor

2024-02-17T04:18:16.3966667+00:00
@King Java
Welcome to Microsoft Q&A platform and thanks for posting your question. I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others "I'll repost your solution in case you'd like to accept the answer.

__Ask: __I am trying to find a better solution to not clone 50+ pipelines. Basically, I have about 50+ REST API endpoints: Example:

https://api.example.com/Provider-Info?providerUid=1 https://api.example.com/Provider-Info?providerUid=2 .... https://api.example.com/Provider-Info?providerUid=51 I have created a simple pipeline to test using "Copy data", and it is working: I have source as REST (linked service): I am trying to modify this pipeline so that it dynamically alternates providerUid (from static like I have now). So, data gets pulled from REST API --> Azure SQL Server table. I am trying to alternate providerUid each time and iterate so that data gets inserted into Azure SQL for 50+ times using one pipeline. What is best architecture to make this work?

Solution:

Here are screenshots: Inside ForaEach --> CopyData: Results: WafaStudios' video gave me a key (@item()): https://www.youtube.com/watch?v=KuWYuHlUwD0

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information. If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

---Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.
Please sign in to rate this answer.
phemanth 11,280 Reputation points Microsoft Vendor

2024-02-19T04:28:23.8266667+00:00

@King Java Following up to see if the above answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

1 additional answer

King Java 520 Reputation points

2024-02-17T00:50:18.3266667+00:00

I was able to figure out using ForEach and Parameter after many trials :) .

Here are screenshots:

Inside ForaEach --> CopyData:

Results:

WafaStudios' video gave me a key (@item()):

https://www.youtube.com/watch?v=KuWYuHlUwD0
Please sign in to rate this answer.
phemanth 11,280 Reputation points Microsoft Vendor

2024-02-17T04:27:59.79+00:00

@King Java Glad to know your issue has been resolved. Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others "I'll repost your solution in case you'd like to accept the answer.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Your answer