Generating JSON file using CopyData activity in ADF pipeline

Question

Generating JSON file using CopyData activity in ADF pipeline

Amit Trivedi 40

I am running below sql query in the source of CopyData activity in ADF pipeline which generate a JSON output as given below. The source is the Azure sql and the target location is the data lake.

User's image

The issue is : If you see the Location in the JSON, it is adding backslash along with the forward slash (forward slash is part of the data). Also, the final json file is having backslash in start and end of everywhere in the json which are not visible here. Please suggest way to remove these backslashes while generating the JSON file

KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator

2023-10-12T01:47:53.99+00:00

@Amit Trivedi Thanks for using Microsoft Q&A forum and posting your query.

Looks like using the For JSON Path is creating this issue. Could you please try to do a straight query using lookup and then try to form your target json structure using mapping dataflow transformations? That should help avoid this error.

When you are using JSON path query from your source, ADF is treating output from your query as the string and hence resulting in such additional escape characters.

Hence to avoid this issue, kindly try with Mapping data flow to transform the data and let us know if you notice any further issues.

Hope this info helps.

Thank you

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.
KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator

2023-10-17T00:22:35.4733333+00:00

@Amit Trivedi Checking to see if you have got a chance to try with mapping data flow? If so let us know how it goes.

Thank you
KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator

2023-10-19T23:25:16.8266667+00:00

@Amit Trivedi We still have not heard back from you yet. Checking to see if you are still blocked or doesn using Mapping data flows helped resolve the issue?

Thank you

Your answer

KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator

2023-10-12T01:47:53.99+00:00

@Amit Trivedi Thanks for using Microsoft Q&A forum and posting your query.

Looks like using the For JSON Path is creating this issue. Could you please try to do a straight query using lookup and then try to form your target json structure using mapping dataflow transformations? That should help avoid this error.

When you are using JSON path query from your source, ADF is treating output from your query as the string and hence resulting in such additional escape characters.

Hence to avoid this issue, kindly try with Mapping data flow to transform the data and let us know if you notice any further issues.

Hope this info helps.

Thank you

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.
KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator

2023-10-17T00:22:35.4733333+00:00

@Amit Trivedi Checking to see if you have got a chance to try with mapping data flow? If so let us know how it goes.

Thank you
KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator

2023-10-19T23:25:16.8266667+00:00

@Amit Trivedi We still have not heard back from you yet. Checking to see if you are still blocked or doesn using Mapping data flows helped resolve the issue?

Thank you

Share via

Generating JSON file using CopyData activity in ADF pipeline

Your answer