Hello! We've been trying to create a pipeline to call an API endpoint. I couldn't use Rest API linked service because it doesn't allow for client certificates (.pfx with a password). The only linked service that worked was a HTTP linked service. We used that and built our pipeline. Then, when we got the output, we got just 1 level down in the hierarchy ( so, for example, we have people ID's but within each person, there's an array with multiple values assigned to each person like positions). So it works to get all the people, but not to go deeper in the nested json. All the solutions on the internet suggest using Data Flow for more complex transformations, especially with nested json with so many levels. However, Data Flow doesn't seem to work with HTTP linked service. can you suggest a workaround for this? Is there a solution, which we can use with Data Flow to get all the levels of nested json, and that will take client certificates with a password in the linked service?

Hi @60185333 , Welcome to Microsoft Q&A forum and thanks for reaching out here. Yes, HTTP is not supported as source in Data flows and hence you won't be able to use it directly to flatten the complex JSON data from your HTTP Endpoint. But as a workaround what you can do is to use the HTTP linked service to call the API endpoint and retrieve the JSON data to a blob storage as staging location using a copy activity, and then use the Data Flow activity next to copy activity and point data flow source to the HTTP response json file to transform the complex JSON data and extract the nested JSON values as needed. Once you have the HTTP response data in storage blob, then in mapping data flow, you will have to use Flatten transformation to flatten the nested values. Hope this helps. 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.

ADF Https linked service doesn't work with Data Flow

Accepted answer

KranthiPakala-MSFT 46,602 Reputation points Microsoft Employee

2023-05-19T23:52:23.39+00:00

Hi @60185333 ,

Welcome to Microsoft Q&A forum and thanks for reaching out here.

Yes, HTTP is not supported as source in Data flows and hence you won't be able to use it directly to flatten the complex JSON data from your HTTP Endpoint.

But as a workaround what you can do is to use the HTTP linked service to call the API endpoint and retrieve the JSON data to a blob storage as staging location using a copy activity, and then use the Data Flow activity next to copy activity and point data flow source to the HTTP response json file to transform the complex JSON data and extract the nested JSON values as needed.

Once you have the HTTP response data in storage blob, then in mapping data flow, you will have to use Flatten transformation to flatten the nested values.

Hope this helps.

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.

1 person found this answer helpful.
60185333 20 Reputation points

2023-05-23T03:19:56.5133333+00:00

Thank you for that!

This definitely helped! My only concern is that the API has pagination that's why there are several iterations in the for each activity. I've noticed several times that during pipeline run, some of the copy activities would fail. If i rerun the pipeline, some other copy activities would fail. Like, for example, there are 30 iterations of the copy activity. The first time I ran the pipeline 2 & 3rd iteration failed with an error attached, whereas I am sure the linked service ends with "/" as the other iterations don't fail. The next time I run the pipeline, 2&3rd iterations don't fail, but some other iterations would. Therefore, I have 2 questions.

As a workaround, if i run the pipelines twice, would the iterations have the same data? like the second time I ran the pipeline, I got the json files from 2&3rd iterations. Would those be the same as the missing 2 files from the first iteration? Or is it possible that it would have different data. (The API is retrieving past data, so it's not constantly changing) Also, if I enable fault tolerance, would I lose some faulty iterations?

I've copied all the files I got from each copy activity (the ones that didn't fail) and merged them into 1 file in the pipeline (they have the same schema). However, when I used it as a source in the data flow, it gave me the following error attached. Would it be better to point the data flow at the multiple files from the copy pipeline activity instead of the merged file?

KranthiPakala-MSFT 46,602 Reputation points Microsoft Employee

2023-05-25T14:15:15.8766667+00:00

@60185333 Thanks for getting back.

Not sure why about this behavior unless someone take a look into the error message and it's logs. But I recommend executing the forEach activity in Sequential rather than parallel and see if that helps.

From the above data flow error message seems like the issue is with your data flow source format configuration. Please ensure that JSON settings under Source options is configured appropriately.

For more info about this setting, please refer to this document: Mapping data flow - JSON Source format options

As the original ask of this thread has been answered, 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.

And if you have further questions regarding the new errors you are seeing, please open a new thread so that it will have high visibility across the community

Thank you
Sign in to comment

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

Share via

ADF Https linked service doesn't work with Data Flow

0 additional answers

Your answer