question

MichaelLarsen-8252 avatar image
0 Votes"
MichaelLarsen-8252 asked SaurabhSharma-msft commented

Read JSON array from REST

I am trying to use data factory to get some ddata from a REST api that returns a JSON array.
Data factory insists it cannot read the response even though it is a proper JSON array being returned.

I am using the copy data activity with the REST source.
It should end in an SQL sink but neveer reaches that point.

The source url is https://esi.evetech.net/latest/alliances/?datasource=tranquility giving a response of [99000006,99000008,99000025,99000026,99000036,99000050.......]

Ho do I go about reading the response and saving it a database?

azure-data-factory
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.

SaurabhSharma-msft avatar image
0 Votes"
SaurabhSharma-msft answered SaurabhSharma-msft edited

@michaellarsen-8252 Thanks for using Microsoft Q&A !!

I do not think you can get this data using REST source in copy data as this is a JSON array of numbers. In order to get this data one approach is to fetch JSON data using HTTP Connector as a source in Copy Data Activity and store this data as a CSV file on a Blob storage as a staging as some scrubbing (removal of '[' ) is required.
You then need to use another Copy Activity to pull from staging to final SQL destination considering input as a csv data file. I have tried in my environment and it worked for me.

You could try this in your environment if this solution works for you. Please find below the GIF for your reference

Please let me know if you have any questions


Please do not forget to "Accept the answer" wherever the information provided helps you to help others in the community.

· 1
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.

MichaelLarsen-8252 avatar image
0 Votes"
MichaelLarsen-8252 answered SaurabhSharma-msft commented

Thank you for the detailed answer.

I have tried it and after a few attempts I got it working enough that I could get the data into a table.
The only thing I could not figure out as how to remove the "[" from the first row and the "'" from the last row.
Fortunately that is fairly simple oncee I have the data in my table.

· 3
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.

I tinkered with it a bit bit more and got it down to a copy data activity saving it in my table directly.
Turns out I need a stored procedure to clean up the data as the last item also contains CRLF characters. I could not figure out how to get rid of those as well in the copy data activity.
I then saved the result as strings in a table and after cleaning, I had my desired datast.

1 Vote 1 ·
0 Votes 0 ·

@michaellarsen-8252 Thanks. Yes, it is right to getting rid of the [ ] in SQL. However, You may have missed it in the above animation that I am getting rid of the [ and ] in the copy activity using Row delimiter as ] in HTTP Source (in first copy data activity) -

83343-image.png

and by adding as Escape character in STG source of 2nd data activity ![83258-image.png

If you can get rid of the [ ] brackets in your SQL then you can try using a single copy data activity in the above pipeline instead of two by using row delimiter as (,) comma
83306-image.png


0 Votes 0 ·
image.png (47.5 KiB)
image.png (49.2 KiB)
image.png (40.9 KiB)