Error occurred when deserializing source JSON file

Dheepak Sreedharan 20 Reputation points
2023-09-25T20:35:42.38+00:00

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
Preview Data Error

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
Synapse Source Settings

How to I resolve this error?

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,997 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,843 questions
0 comments No comments
{count} votes

Accepted answer
  1. ShaikMaheer-MSFT 38,456 Reputation points Microsoft Employee
    2023-09-26T16:31:57.64+00:00

    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.


3 additional answers

Sort by: Most helpful
  1. Amira Bedhiafi 26,186 Reputation points
    2023-09-26T09:18:16.4666667+00:00

    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.


  2. Dheepak Sreedharan 20 Reputation points
    2023-10-03T14:32:33.9866667+00:00

    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.

    0 comments No comments

  3. Kwitaro 0 Reputation points
    2024-05-02T12:31:26.43+00:00

    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

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.