Data copy from ADLS to SQL Database using RestAPI

G Rohini Bharadwaja 46 Reputation points
2022-11-16T07:40:51.53+00:00

Hi All,

I have task to copy JSON data from ADLS to SQL Database using Rest API and in my scenario I am using Logic App which is pre configured with the database details.

I have tried using CopyData Activity in ADF to achieve this but when I debug, the activity is getting succeeded and the data is not getting copied, instead it is showing as 0bytes transferred.

Not sure, how to achieve the task.
Can any one help here.

Thanks,
GRBharadwaja
260724-screenshot-20221116-125725.png

Azure SQL Database
Azure Data Lake Storage
Azure Data Lake Storage
An Azure service that provides an enterprise-wide hyper-scale repository for big data analytic workloads and is integrated with Azure Blob Storage.
1,416 questions
Azure Logic Apps
Azure Logic Apps
An Azure service that automates the access and use of data across clouds without writing code.
2,969 questions
{count} votes

Accepted answer
  1. AnnuKumari-MSFT 31,996 Reputation points Microsoft Employee
    2022-11-17T08:27:53.287+00:00

    Hi @G Rohini Bharadwaja ,

    Thankyou for using Microsoft Q&A platform and thanks for posting your question here.

    As I understand your ask, you are trying to copy data from json file into SQL database via logic app being called by ADF pipeline. Please let me know if my understanding is incorrect.

    I tried to replicate your scenario and found few things that is worth mentioning here:

    1. Logic app URL is not a REST API url , when you see the logic app designer, it says, HTTP Post URL which means we can't use REST API connector to call logic app . It should be an HTTP connector. Additionally, copy activity sink doesn't accept HTTP connector . Therefore, you need to call the logic app via Web activity where we can provide the logic app HTTP URL directly.
    2. When we are trying to copy data from JSON file to SQL table, basically , we are trying to insert record in the table. In logic app, till now there is no option to perform batch insertion in the table. We can only insert data from single json to SQL table , not array of JSON. If your requirement is to insert multiple records , then you need to use lookup to read the JSON file and then iterate through each JSONs via foreach activity in ADF and call the web activity(to run logic app) inside ForEach.

    Please check the below video for reference:

    261363-jsontologicapp.gif

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

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

    • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you.
      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
    • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

0 additional answers

Sort by: Most helpful