Read JSON array from REST

Michael Larsen 26 Reputation points
2021-03-30T06:34:27.23+00:00

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
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,335 questions
0 comments No comments
{count} votes

Accepted answer
  1. Saurabh Sharma 23,661 Reputation points Microsoft Employee
    2021-03-31T00:46:40.197+00:00

    @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 additional answer

Sort by: Most helpful
  1. Michael Larsen 26 Reputation points
    2021-03-31T13:20:22.183+00:00

    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.