Error occurred when deserializing source JSON file

Question

I am doing 'Copy Data' from my Vendor's API to ADLS Gen2 CSV sink.

The endpoint works in Postman, and returns Content-Type as text/plain;charset=ISO-8859-1

I checked with my API vendor, there is no JSON output offered. I configured the REST connection and I get this error When I try to preview the data

When I run the pipeline, I get the following error

{
    "errorCode": "2200",
    "message": "Failure happened on 'Source' side. ErrorCode=JsonInvalidDataFormat,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Error occurred when deserializing source JSON file ''. Check if the data is in valid JSON object format.,Source=Microsoft.DataTransfer.ClientLibrary,'",
    "failureType": "UserError",
    "target": "PL_Copy_API",
    "details": []
}

Here is my source settings

How to I resolve this error?

Answer 1

Hi Dheepak Sreedharan,

Thank you for posting query in Microsoft Q&A Platform.

Kindly mention content-type value as application/json and see if that helps. Please note, your API should have actual support for json data format, if not it will not work.

Hope this helps. Please let me know if any further queries.

---

Please consider hitting Accept Answer button. Accepted answers help community as well. Thank you.

Answer 2

Since the response from the API was text/plain, when I used application/json as suggested, the error kept occurring.

In order to fix this, I have configured my connection as HTTP instead of REST.

Answer 3

Hi,

I still get the exact same error when collecting the following json from a REST end-point:

[

"some string 1",

"some string 2",

"some string 3"

]

Any ideas?

Thanks,
Lars

Answer 4

If you check the ErrorCode=JsonInvalidDataFormat, you will understand that the pipeline is expecting the data from your vendor's API to be in a JSON format. However, as you mentioned, the response from the API has a Content-Type of text/plain;charset=ISO-8859-1, which means it's returning plain text and not JSON and since your API is returning plain text, you should configure your source dataset to expect plain text instead of JSON.