Json response could not parse successfully in Copy activity (source)

@Quyen Dang Thanks for reaching out to Microsoft Q&A

Check JSON Structure: Ensure that the JSON structure is consistent and correctly formatted. Sometimes, even a small deviation can cause parsing issues.

The JSON response you provided includes double quotes within string values like "MerchantName" and "MerchantRegistrationNumber" without escaping them. For example:

"MerchantName": "Company Name" LTD",

This is not valid JSON syntax. JSON requires double quotes within strings to be escaped using a backslash (\).

To resolve this, you need to ensure the source system provides a valid JSON response. The double quotes within the string values should be escaped. Here's how the corrected JSON should look:

{
"Transactions": [
{
"MerchantName": "Company Name LTD",
"MerchantRegistrationNumber": "HE...",
"TransactionID": "4...", 
"Arn": "746...",
"PurchaseDate": "2024-09-10T00:00:00Z",
"RegistrationDate": "2024-09-11T00:00:00Z"
}
]
}

Data Type Compatibility: Verify that the data types in the JSON response are compatible with the schema defined in the Copy activity. Incompatible data types can lead to parsing errors.

could you please more details of your copy activity configuration with screenshots if possible, to investigate further.

Hi,

I might have over-erased the info, but I can ensure you that the format was correct. The json response was from a real, in-production REST API call (that was not built by me or anyone I might know), so the syntax should not be of question.

This is the screenshot of the Copy activity > Source along with the additional headers.

@Quyen Dang

Thanks for sharing the screenshot! Based on the details provided, here are a few additional steps you can take to troubleshoot the issue:

Verify Dataset Properties: Double-check the dataset properties in the Source settings. Ensure that the schema and data types match the expected structure of the JSON response.

Test with Postman or Similar Tool: Use a tool like Postman to make the same API call and verify the response. This can help confirm that the API is returning the expected JSON format.

Debugging Logs: Check the activity logs for any error messages or warnings that might provide more insight into why the Copy activity is failing.

Simplify the JSON Response: If possible, try simplifying the JSON response to a smaller subset of data to see if the issue persists. This can help isolate the problem.

Review Data Mapping: Ensure that the data mapping between the source and sink is correctly configured. Any mismatches can lead to parsing issues.

If the problem continues, please provide more details about the specific error messages you’re encountering,

@Quyen Dang 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 with more details and we will try to help.

i recreated the pipeline again from scratch and it was fine. :) so it was probably something insignificant in the setup. thank you for checking!