Json file from SQL Table using ADF is adding Escape characters

Question

I ma trying to create json file from SQL table using ADF through Stored Procedure

Stored Procedue:

SELECT ( SELECT * FROM [dbo].[TEST_TABLE] FOR JSON PATH, INCLUDE_NULL_VALUES ) AS json_file;

I am then trying copy activity where Source is SQL Table and Sink is ADLS json but the o/p is coming as

"json_file":"[{"FIL_PATH":"\/mnt\/test\/\/test_table\/jsonfiles","ACTV_IND":"Y","CREATED_DATE":"2024-04-17T20:56:57.870","UPDATED_DATE":"2024-04-17T20:56:57.870","VB_OCCURS":"N"}]"}

I have eevn tried creating as a comma deleimited file instead of json as sink with the below but still it doesn't help

Pratik Lad · Accepted Answer · 2024-04-21 09:59:32Z

1

Not to get added escape characters in Json file you need to sore the data in Json file with delimited file dataset.

My sample stored procedure Output looks like:

enter image description here

To store this into Json file without escape characters you need to use delimited file dataset with below settings:

Add column delimiter is the character which is not present in your data.
Add quote character and escape character as No quote character and No escape character respectively.

enter image description here

Output:

enter image description here

answered Apr 21, 2024 at 9:59

Pratik Lad

8,7282 gold badges5 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

newbie Over a year ago

Thanks Pratik. It worked for the most part except the below. Table data : "/mnt/test/test_table/jsonfiles" Was previously coming as : "\\/mnt\\/test\\/\\/test_table\\/jsonfiles\" but now coming as "\/mnt\/test\/test_table\/jsonfiles\"

newbie Over a year ago

Its' due to the fact that when I run the Select Query from the table SELECT ( SELECT * FROM [dbo].[TBL_DATASET_CONFIG] FOR JSON PATH, INCLUDE_NULL_VALUES ) AS approved_dataset it includes the Escape characters. How to get rid of them?

Collectives™ on Stack Overflow

Json file from SQL Table using ADF is adding Escape characters

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related