question

NageshCL-5231 avatar image
0 Votes"
NageshCL-5231 asked ShaikMaheer-MSFT commented

JSON source in ADF

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

127018-image.png


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

127141-image.png



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?

azure-data-factory
image.png (17.0 KiB)
image.png (19.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

ShaikMaheer-MSFT avatar image
1 Vote"
ShaikMaheer-MSFT answered ShaikMaheer-MSFT commented

Hi @NageshCL-5231 ,

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;

127414-lookup.gif

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

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

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.
127417-derviedcolumn.gif

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.



lookup.gif (987.9 KiB)
image.png (110.9 KiB)
derviedcolumn.gif (401.4 KiB)
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ShaikMaheer-MSFT,

Thanks for suggesting the work around.
With this, all the rows / json documents will be in a single column. I would need to split in multiple rows and do some transformations. Not sure if I would be able to do it?

Regards,
Nagesh CL

0 Votes 0 ·

Hi @NageshCL-5231 ,

Thank you for follow up query.

So , it seems your ultimate intention is to take data in to dataflows to do transformations. Then, Why we are taking data as json?

Best way is take data in tabular format only from source and apply transformations on it(I mean avoid "FOR JSON AUTO" in your query. Use direct select query)

Taking rows from table as json in to dataflows and again converting that json as rows not make sense.

If your intention is saving your transformed data as json that in sink transformation you can have json format dataset. That will do the job.

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

Please do consider clicking on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members


0 Votes 0 ·

Hi @ShaikMaheer-MSFT ,

Finally that's what i did. Took data in table format from source, converted into JSON in mapping data flow and then loaded it in target (cosmos DB). To create JSON structure, I had to create columns and sub-columns structure in derived column which was a little cumbersome task. So thought sql query was quick and neater approach.

The reason i chose dataflow is because, i had to compare the json and do upsert if the structure is changed (typical SCD1 which could not be achieved in copy data activity).

Thanks for your prompt response and help. Appreciated.

Regards,
Nagesh CL

0 Votes 0 ·
Show more comments