Copy Activity error in Synapse Pipelines

Abhra Mitra 0 Reputation points
2024-03-14T14:37:37.3333333+00:00

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.

Azure Data Lake Storage
Azure Data Lake Storage
An Azure service that provides an enterprise-wide hyper-scale repository for big data analytic workloads and is integrated with Azure Blob Storage.
1,343 questions
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,372 questions
Azure Databricks
Azure Databricks
An Apache Spark-based analytics platform optimized for Azure.
1,921 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,718 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,549 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Amira Bedhiafi 15,216 Reputation points
    2024-03-14T16:34:32.2333333+00:00

    Double-check the JsonNodeReference and JsonPathDefinition settings in your Copy Activity and verify that the path provided correctly points to the data you intend to copy.

    • Since you have other similar pipelines working correctly, compare the configurations between the one that's failing and the ones that are not. Look for any discrepancies in settings, especially around the JSON source or path definitions.
    • As a troubleshooting step, try using a simplified version of your JSON file that only includes a minimal set of data. This can help identify if a specific section of the JSON is causing the issue.

    0 comments
  2. AnnuKumari-MSFT 30,751 Reputation points Microsoft Employee
    2024-03-20T10:06:38.75+00:00

    Hi Abhra Mitra ,

    I understand that you are encountering an error when attempting to copy data to SQL Server from a JSON file located in the Datalake.

    Kindly make sure to mention header as "content-type" value as application/json . Additionally, re-check if the json is a valid json or not.

    Hope it helps. Kindly accept the answer by clicking on Accept answer button . Thankyou

    0 comments