ADF Copy Data REST API , putting JSON response to SQL Table

Mav 81

Hi,
I am trying to call an API using REST connector in COPY DATA .
However when I use SQL Table in SINK and run pipeline, the JSON is not getting stored in the table

instead the NULL value is being inserted into the table.

I have not defined any mapping as I want full JSON string to be inserted into one column of the table defined in SINK

Any help ?

ShaikMaheer-MSFT 37,896 Reputation points Microsoft Employee

2022-03-04T06:32:35.947+00:00

Hi @devhere - Just check is below answer helps. If yes, Please consider marking it as Accepted Answer. Accepted answers helps community as well.

1 answer

ShaikMaheer-MSFT 37,896 Reputation points Microsoft Employee

2022-02-07T08:18:43.05+00:00

Hi @devhere ,

Thank you for posting query in Microsoft Q&A Platform.

Using Copy activity with REST connector as source data source will not help in this case, as your requirement is store entier json as string in to your sink table column.

Kindly consider below implementation.

Use web activity to make API call and get json. Then use Set variable activity to store web activity output json as string in to an variable. Then use copy activity additional column field to add extra column on source dataset for this json data. Then under copy activity mappings, map your additional column to desired sink table column.

Please check below screenshots for better idea.

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

-----------

Please consider hitting Accept Answer. Accepted answers helps community as well.
Please sign in to rate this answer.

2 people found this answer helpful.
Mav 81 Reputation points

2022-02-07T20:22:25.797+00:00

thanks Shaik for helping.However I checked my webactivity is returning error "The length of execution output is over limit (around 4MB currently)"

seems I cant use webactivity

ShaikMaheer-MSFT 37,896 Reputation points Microsoft Employee

2022-02-08T16:56:49.173+00:00

Hi @devhere ,

Yes that's limitation. If size is more then 4MB then web activity cannot be used. You may try using Azure functions if that helps.

----------

Please consider hitting Accept Answer. Accepted answers helps community as well.

Mav 81 Reputation points

2022-02-09T05:42:55.483+00:00

Can you give the example with azure functions as I’m really new to this

ShaikMaheer-MSFT 37,896 Reputation points Microsoft Employee

2022-02-11T15:55:42.953+00:00

Hi @devhere,

Azure functions helps to write code in C# or Python or PowerShell and deploy it to Azure in serverless fashion.

So I mean to say was implement your own code logic to perform similar task and deploy it to Azure function and run it.

Click here to know more about azure functions.

-------
Please consider hitting Accept Answer. Accepted answers helps community as well.

ShaikMaheer-MSFT 37,896 Reputation points Microsoft Employee

2022-02-15T16:38:57.553+00:00

Hi @devhere,

Just checking in to see if the below answer helped. If this answers your query, do click and upvote for the same. And, if you have any further query do let us know.
Sign in to comment