JSON source in ADF

Question

Hi Team,

I have a source which is a azure sql DB. The output from that source is JSON. Query and sample output is as below: -

SELECT * FROM dbo.SalesHeader SH
INNER JOIN dbo.SalesDetail SD ON SH.SalesOrderID = SD.SalesOrderID
FOR JSON AUTO

The issue is, when i place this sql as source query in ADF mapping data flow, i get an error as below:-

How to resolve this issue? How to get the JSON output in a single column? Single row per SalesOrderID and the JSON structure in a single column?

Answer 1

Hi @Nagesh CL ,

Thank you for posting query in Microsoft Q&A Platform.

Yes unfortunately using "FOR JSON AUTO" sql query directly in Mapping data flows ends with this error.

You may consider below work around to avoid these error.

Use the query in lookup activity, and pass your json data string to data flow parameter. You need to have a parameter created inside your data flow. Use derived column transformation to generate new column on top of your data and save these parameter as value in to that column.

Below is detailed implementation of same.

Step1: Lookup activity to run your query of "FOR JSON AUTO"
code used:

DECLARE @json NVARCHAR(MAX);  
SET @json=(select * from dbo.tbl_employees FOR JSON AUTO);  
SELECT @json as JsonData;  

Step2: Pass lookup activity output to dataflow parameter
pipeline expression used: @activity('Lookup1').output.firstRow.JsonData

Step3: Source Transformation which points to some dummy file with single column and single row data and parameter created inside data flow.

Step4: Derived Column transformation to add new column with data flow parameter as value. So that you can get or json as value inside new column.

Once above steps done, you can use select transformation to get only your jsonData column.

Hope this will help. Please let us know if any further queries. Thank you.

-----------------------

• Please accept an answer if correct. Original posters help the community find answers faster by identifying the correct answer. Here is how.
• Want a reminder to come back and check responses? Here is how to subscribe to a notification.