Azure Synapse - SQL DW Copy Command doesn't support Source dataset type as 'JsonFormatV2'?

Question

Receiving the following error when attempting to copy JSON files to a SQL DB (dedicated pool) using the copy command in a copy data activity. Also ran into the issue when using an Azure Blob Storage linked service for the source dataset.

Source dataset: Azure Data Lake Storage Gen2
Sink dataset: Azure Synapse dedicated SQL pool

Error details:

{
"errorCode": "2200",
"message": "ErrorCode=UserErrorSqlDWCopyCommandError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=SQL DW Copy Command doesn't support Source dataset type as 'JsonFormatV2',Source=Microsoft.DataTransfer.ClientLibrary,'",
"failureType": "UserError",
"target": "<pipeline_name>",
"details": []
}

Answer 1

Hi @Jacob Wadowski ,

Welcome to Microsoft Q&A platform and thanks for posting your question.

It seems you are trying to copy the data from JSON file loaded into ADLS gen2 to Dedicated SQL pool using Synapse pipelines. However, you are receiving the above mentioned error. Please let me know if my understanding is incorrect.

I tried to repro your case but didn't find any issue loading JSON data into DedSQLPool. Could you please share your sample JSON data so that I can validate why it is being treated as JSONFormatV2 . Thanks in advance for your efforts.

Answer 2

Other folks may correct me but the only formats supported for copy into Dedicated SQL pool are CSV, Parquet and ORC.

In your case, you could change your pipeline to first translate your JSON data to Parquet and then import the Parquet data into your SQL DB. Like so: