post data into rest API using ADF

Question

post data into rest API using ADF

Dadheech, Raveesh 41

Hi Team,

Here my requirement is to insert bulk records into rest-api using ADF, i have created pipeline and succeed by using these three steps of pipeline:

Web Activity [Fetching data from another API ]
Set variable [Store output of web activity into Array type variable]
Inside For each added new Web activity to process value of Variable for each iteration . Done
But this approach taking so much time in case of records are large [ i understood limit of variable and web activity but here just need help on speeding data transferring ]
Question 1 : Can we post whole output of Array Variable into post request body in single attempt , as destination api support to take bulk records .
Question 2 : Can we Pick specific fields from response of Step 1 , as it's an array and can not be stored into a string variable ,so not able to play with response before pushing into step 3 activity as @variable('data')
Note : As i tried previously with copy activity also but i am surprised that pipeline get executed but records not executed into destination .

                                           Thanks for your acknowledgment .

Your suggestion would be appreciated, struggling for this issue from last 5-7 weeks .

Sample of Request Input for destination API:

"operation": "create", 
"bulk": true,
"resourceName": "DestinationtableRest", 
"data": 
		[ {
         "A":"1"....},
          {
          "A": "2"         
          }
        ]

KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2023-02-23T01:07:48.2466667+00:00

Hi @Dadheech, Raveesh ,

Welcome to Microsoft Q&A forum and thanks for reaching out here.

It will be great if you could share few screenshots or sample of how your API response message looks like and how it looks like after saving it to array variable and how you are passing it to destination API in the request body. A few additional details will give more clarity of what exactly is happening.

Below is my response based on the initial understanding of the requirement:

Regarding your first question, you can use the Copy Activity to copy the entire output of the Array Variable into the post request body in a single attempt. You can use the Sink section of the Copy Activity to specify the destination API and the request body. First, I would recommend testing it in Postman or some other tool to make sure you are able to send such POST Body requests to the API successfully without any issue. Then try to hard code array variable values in ADF Copy activity POST Body and test it. Finally, after making sure, it is successful with hardcoded values in the POST Body, then pass the array variable using dynamic expression in the request POST Body.

Regarding your second question, at this point as I don't know how your source API Array response look like, but in general you pick specific fields if you know the index of those fields. Please provide a sample data for reference and let us know which specific fields you would like to pick from the API response. But in general, For Each activity approach you have already followed is the ideal approach.

Hope this helps. Feel free to share additional details with samples that way other community member can be helpful in understanding the requirement better and can assist accordingly.
Dadheech, Raveesh 41 Reputation points

2023-02-23T11:26:31.8433333+00:00
Hello KranthiPakala-MSFT

Thanks for the response,

On your suggestion of using copy activity i have already tried it first before this approach and seen result as shown below:

Image 1: Output of Copy Activity > as we can see it showing records has been written in Destination , but actually records not inserted there (Validated many times by re-running this pipeline , even Source API and Destination AI have same column , but i used mapping tab for required mapping )

Image 2: Detail of SOURCE and SINK

i have tested and validated in postman with declared data format .

Sample of Destination API request body is :

{ "operation": "create", "bulk": true, "resDataSet": "AppDor", "data": [ "id", "CreatedBy", "CreatedDate", "EmployeeNumber", "Activity" , "PostingDate" ], "data": [ { "id":1 "CreatedBy": "User1", "CreatedDate": "2022-01-10", "EmployeeNumber" : "T10001", "Activity" :"Track" , }, { "id":2 "CreatedBy": "User2", "CreatedDate": "2022-01-15", "EmployeeNumber" : "T10001", "Activity" :"Track" , }, ] }
Dadheech, Raveesh 41 Reputation points

2023-02-23T11:32:30.08+00:00

Thanks @KranthiPakala-MSFT

i have already tried it with copy activity as SS shown below , but when actually record not inserted into destination (even tested APi in postman and able to insert records)

Image 1: Output of Copy Activity

Image 2: Source and Destination Mapping in copy activity

Json sample used for posting data in destination api :

{ "operation": "create", "bulk": true, "resourceName": "AppDoctor", "fields": [ "id", "CreatedBy", "CreatedDate", "EmployeeNumber", "Activity" , "PostingDate" ], "data": [ { "id":1 "CreatedBy": "User1", "CreatedDate": "2022-01-10", "EmployeeNumber" : "T10001", "Activity" :"Track" , }, { "id":2 "CreatedBy": "User2", "CreatedDate": "2022-01-15", "EmployeeNumber" : "T10001", "Activity" :"Track" , }, ] }
Dadheech, Raveesh 41 Reputation points

2023-02-27T07:32:41.1766667+00:00

Hi @KranthiPakala-MSFT ,

i have shared all required detail as asked in conversation , kindly let me know in case or any more detail require to be share.
Dadheech, Raveesh 41 Reputation points

2023-03-02T18:17:51.5433333+00:00

Hi Team,

May i expect any suggestion from your side for my detailed concern .
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2023-03-20T22:16:59.3766667+00:00

@Dadheech, Raveesh Sorry for getting back late on this.

I see that you are getting multiple data array object records and pass them as body to your endpoint. If that's the case, then you cannot use REST connector in COPY Sink as you cannot pass the body in COPY REST SINK. What I would suggest is to use an append variable inside the forEach and append the required data object to an array variable and then you can use that append variable (array) in the body of a web activity using dynamic expression which will achieve your requirement.

Let me know if you have any questions.

Thank you

1 answer

Your answer

KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2023-02-23T01:07:48.2466667+00:00

Hi @Dadheech, Raveesh ,

Welcome to Microsoft Q&A forum and thanks for reaching out here.

It will be great if you could share few screenshots or sample of how your API response message looks like and how it looks like after saving it to array variable and how you are passing it to destination API in the request body. A few additional details will give more clarity of what exactly is happening.

Below is my response based on the initial understanding of the requirement:

Regarding your first question, you can use the Copy Activity to copy the entire output of the Array Variable into the post request body in a single attempt. You can use the Sink section of the Copy Activity to specify the destination API and the request body. First, I would recommend testing it in Postman or some other tool to make sure you are able to send such POST Body requests to the API successfully without any issue. Then try to hard code array variable values in ADF Copy activity POST Body and test it. Finally, after making sure, it is successful with hardcoded values in the POST Body, then pass the array variable using dynamic expression in the request POST Body.

Regarding your second question, at this point as I don't know how your source API Array response look like, but in general you pick specific fields if you know the index of those fields. Please provide a sample data for reference and let us know which specific fields you would like to pick from the API response. But in general, For Each activity approach you have already followed is the ideal approach.

Hope this helps. Feel free to share additional details with samples that way other community member can be helpful in understanding the requirement better and can assist accordingly.
Dadheech, Raveesh 41 Reputation points

2023-02-23T11:26:31.8433333+00:00

Hello KranthiPakala-MSFT

Thanks for the response,

On your suggestion of using copy activity i have already tried it first before this approach and seen result as shown below:

Image 1: Output of Copy Activity > as we can see it showing records has been written in Destination , but actually records not inserted there (Validated many times by re-running this pipeline , even Source API and Destination AI have same column , but i used mapping tab for required mapping )

Image 2: Detail of SOURCE and SINK

i have tested and validated in postman with declared data format .

Sample of Destination API request body is :

{ "operation": "create", "bulk": true, "resDataSet": "AppDor", "data": [ "id", "CreatedBy", "CreatedDate", "EmployeeNumber", "Activity" , "PostingDate" ], "data": [ { "id":1 "CreatedBy": "User1", "CreatedDate": "2022-01-10", "EmployeeNumber" : "T10001", "Activity" :"Track" , }, { "id":2 "CreatedBy": "User2", "CreatedDate": "2022-01-15", "EmployeeNumber" : "T10001", "Activity" :"Track" , }, ] }
Dadheech, Raveesh 41 Reputation points

2023-02-23T11:32:30.08+00:00

Thanks @KranthiPakala-MSFT

i have already tried it with copy activity as SS shown below , but when actually record not inserted into destination (even tested APi in postman and able to insert records)

Image 1: Output of Copy Activity

Image 2: Source and Destination Mapping in copy activity

Json sample used for posting data in destination api :

{ "operation": "create", "bulk": true, "resourceName": "AppDoctor", "fields": [ "id", "CreatedBy", "CreatedDate", "EmployeeNumber", "Activity" , "PostingDate" ], "data": [ { "id":1 "CreatedBy": "User1", "CreatedDate": "2022-01-10", "EmployeeNumber" : "T10001", "Activity" :"Track" , }, { "id":2 "CreatedBy": "User2", "CreatedDate": "2022-01-15", "EmployeeNumber" : "T10001", "Activity" :"Track" , }, ] }
Dadheech, Raveesh 41 Reputation points

2023-02-27T07:32:41.1766667+00:00

Hi @KranthiPakala-MSFT ,

i have shared all required detail as asked in conversation , kindly let me know in case or any more detail require to be share.
Dadheech, Raveesh 41 Reputation points

2023-03-02T18:17:51.5433333+00:00

Hi Team,

May i expect any suggestion from your side for my detailed concern .
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2023-03-20T22:16:59.3766667+00:00

@Dadheech, Raveesh Sorry for getting back late on this.

I see that you are getting multiple data array object records and pass them as body to your endpoint. If that's the case, then you cannot use REST connector in COPY Sink as you cannot pass the body in COPY REST SINK. What I would suggest is to use an append variable inside the forEach and append the required data object to an array variable and then you can use that append variable (array) in the body of a web activity using dynamic expression which will achieve your requirement.

Let me know if you have any questions.

Thank you

Answer 1

Finally i got the solution by implementing pipeline with help of several activities .Just posting here so that it can be help others if have same requirement (i just temporary stored data on blob storage and deleted on execution of pipelines get completed ) Activities

Copy Data activity - Fetch and store data into blob storage (SINK) in CSV format (not json because csv option provide way of specifying max rows which help to store files in chunks of data inside a temporary created folder ex:Input_1234 that can be operated easily by metadata activity as used in next steps )
Used metadata activity with childitems value in fields list option for fetching detail of CSV files as generated by step1
Used foreach loop on basis on copy activity output

   @if(greater(activity('Copyactivity1').output.rowsCopied,0) , activity('MetadataActivity').output.childItems,variables('varEmptyArr'))
   
   *varEmptyArr is a declared variable contain blank value , used in case of no record received form source

Inside foreach i linked step3 to new copy activity for converting CSV data into Json and restored in blob storage on different folder (ex: output_1234)
Then next activity added after step4 is lookup , which pick records from json file and tat can be used in next Web activity body option
Add Web activity for posting data into rest api , used out put of step5 inside body option
Use delete activity for deleting first json file whose records has been pushed to rest api in step 6
Outside of foreach finally again used deleted activity for deleting csv all files from blob
It;s done , but used couple of variables for holding values of parameters which are upto you how to use it in pipeines.
So finally i could say that using data factory you can transfer data from one SAP API/REST api to another restapi. :)

Share via

post data into rest API using ADF

1 answer

Your answer