Copy data from an API to Azure SQL DB

Priya Jha 866 Reputation points
2021-01-14T14:07:13.397+00:00

Hi All,

I need to copy data from an API: https://data.nj.gov/resource/pruu-wapw.json to Azure SQL database

How to proceed with this?

Azure SQL Database
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,758 questions
0 comments No comments
{count} vote

3 answers

Sort by: Most helpful
  1. Saurabh Sharma 23,761 Reputation points Microsoft Employee
    2021-01-14T20:53:32.72+00:00

    @Priya Jha Thanks for using Microsoft Q&A !!

    You can use Copy Data activity with REST dataset to call the API to fetch and load the data in Azure SQL Server.
    56841-image.png

    For the shared API endpoint I could see that it supports anonymous authentication as well, so you need to select authentication type as anonymous while creating the source linked service as shown below -
    56822-image.png

    Please refer to the below gif which creates a new table at source and fetch the data from the above API. Hope this helps.
    56851-rest-api-adf.gif

    Refer to the Copy data from and to a REST endpoint by using Azure Data Factory for additional information about using REST endpoints in Azure Data Factory.

    ----------

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


  2. Azure_researcher 1 Reputation point
    2021-10-27T15:03:37.767+00:00

    Hello! Thanks for your answer! This is what I was looking for!
    I have another questions

    1. Is function "REST" free in free tier Azure account??? I think it is included in Azure Data Factory?
    2. As we have https://data.nj.gov/resource/pruu-wapw.json API we can import data to one DB, but
      what if we have related API with several related tabs like...
      https://data.nj.gov/resource/**pruu-wapw1.json**
      https://data.nj.gov/resource/**pruu-wapw2.json**
      https://data.nj.gov/resource/**pruu-wapw3.json**
      can we use several URL to collect it in one DB (in REST operation)?
      or we must call these operations one after the other but with different URLs to one base?
    0 comments No comments

  3. Souilem, Mahdi 1 Reputation point
    2022-05-05T14:55:27.607+00:00

    Hi,
    i copied data from REST API to azure SQL DB,
    the job run whithout any error message but it return one empty row in my SQL table,
    i did the same job but i replaced blob storage instead sql table and it work perfectly, but with sql table it doesnt return any data,
    can you help me to fix that please ?
    thank you
    199287-image.png

    0 comments No comments