topic Re: Getting a multilevel JSON from an API not working in Fabric Data Factory in Pipelines

Getting a multilevel JSON from an API not working in Fabric Data Factory

PedroCSeq — Fri, 23 Feb 2024 10:30:09 GMT

Hello everyone!
I'm trying to get a multiple JSON files from an API and having issues with a few of them: when using a normal REST connection it returns an invalid opperation (keeping in mind the same exact call works fine on Postman):

Rest call failed with server error, status code 500 InternalServerError, please check your activity settings and remote server issues.

Request URL: [redacted]'.Response: {"returnCode":3,"returnStatus":"INVALID_API_OPERATION"}

Activity ID

de615923-a51c-47ff-9af2-f0b8240c5776

When using a generic http connection with the correct accept statement (application/vnd.mambu.v2+json), it can get to the json but Data Factory doesn't recognize it, throwing this error:

Error occurred when deserializing source JSON file '[redacted]'. Check if the data is in valid JSON object format. Unexpected character encountered while parsing value: <. Path '', line 0, position 0.

I have a notebook that can get to it which I'd like to use only if stricty necessary, because saving jsons with notebooks makes it so I can't name the files saved, only the folders it will be put in and it's somewhat important that the files are named uniformelly.
Anyone knows a method that would work for using the copy activity to get a multilevel JSONs from an API v2?

Re: Getting a multilevel JSON from an API not working in Fabric Data Factory

Anonymous — Sat, 24 Feb 2024 10:07:34 GMT

Hi PedroCSeq ,

Thanks for using Fabric Community.
I can some methods to the JSON deserialization error you're encountering in Fabric Data Factory.

These steps might help:

1. Validate JSON Format:

Manually: Inspect a sample of the response from the API using tools like Postman or a browser.
Review Structure: Make sure it adheres to JSON syntax rules. You can use a JSON validator online or in your code.

2. Address Unexpected Characters:

Trim Leading/Trailing Characters: Remove any extra whitespace or characters before the first curly brace.
Check Data: Ensure the response doesn't contain HTML elements or other non-JSON content.

Hope this is helpful. Please let me know incase of further queries.

Re: Getting a multilevel JSON from an API not working in Fabric Data Factory

Anonymous — Mon, 26 Feb 2024 05:33:01 GMT

Hello PedroCSeq ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.
In case if you have any resolution please do share that same with the community as it can be helpful to others.
Otherwise, will respond back with the more details and we will try to help.

Re: Getting a multilevel JSON from an API not working in Fabric Data Factory

PedroCSeq — Tue, 27 Feb 2024 09:24:57 GMT

I had confirmed that the JSON is valid prior to trying to use copy data, by getting it through notebooks. Also, the link is correct and the only response is in json form, as other calls with simpler json responses to the same main api are working using the same connection.

Re: Getting a multilevel JSON from an API not working in Fabric Data Factory

Anonymous — Wed, 28 Feb 2024 05:10:26 GMT

Hi PedroCSeq ,

Apologies for the issue you have been facing. I would like to check are you still facing this issue?

If its a bug, we will definitely would like to know and properly address it.

Please go ahead and raise a support ticket to reach our support team: Link

After creating a Support ticket please provide the ticket number as it would help us to track for more information.

Thank you.

Re: Getting a multilevel JSON from an API not working in Fabric Data Factory

PedroCSeq — Wed, 28 Feb 2024 10:03:34 GMT

Good morning,

Yes, I'm still getting an error:
Access '[redacted]:search?detailsLevel=FULL' is not allowed on Azure integrate runtime.

This should be a very simple post request on this api

Re: Getting a multilevel JSON from an API not working in Fabric Data Factory

Anonymous — Fri, 01 Mar 2024 07:04:33 GMT

Hello PedroCSeq ,

At this time, we are reaching out to the internal team to get some help on this .
We will update you once we hear back from them.

Re: Getting a multilevel JSON from an API not working in Fabric Data Factory

sreedharshan10 — Wed, 31 Jul 2024 15:37:59 GMT

I have been facing the same issue too.
When I use web activity the output seems to be previewed without any issues but when I try to recreate the same in Copy data I get an error that my JSON output is invalid
I have checked the response in postman and that JSON output seems to be valid
I Would really like to have some clarity on this