I am encountering an error when attempting to copydata to SQL Server from a JSON file located in the Datalake. Specifically, we've encountered the following error:
{
"errorCode": "2200",
"message": "ErrorCode=JsonInvalidArrayPathDefinition,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Error occurred when deserializing source JSON data. Check whether the JsonPath in JsonNodeReference and JsonPathDefintion is valid.,Source=Microsoft.DataTransfer.Common,''Type=Newtonsoft.Json.JsonException,Message=Unexpected character while parsing path indexer: T,Source=Newtonsoft.Json,'",
"failureType": "UserError",
"target": "dwTestFieldsWrite",
"details": []
}
Despite extensive research, I have been unable to pinpoint the exact nature of this error through online resources.
During our troubleshooting efforts, I made several modifications, and on two occasions, the workflow executed successfully, only to fail upon subsequent attempts.
The workflow has 3 steps:
Step1 - Copy Data from SQL Server to ADLS Gen2
Step2 - Notebook(To do transformations in PySpark using Apache Spark Pools)
Step3 - Copy the output JSON from ADLS Gen2 to Datawarehouse (SQL Server)
Note:- I have 5 other similar pipelines doing the same thing but none of them are failing also this workflow works fine with csv format.