Fetch the data from Rest APi and load into Postgresql table dynamically using azure data factory

Question

I have two API, one is to give the counts of the api and second api gives the data .
Using copy activity ,I can fetch the data and load into destination table .
API contains body and header which we need to pass in source under copy activity
Under body there are 5 mandatory parameters required for api to fetch the data i.e
start_row
end_row
email_id
security_pin
cin_login_code

and in Header i .e -
Content-Type
Authorization

**First approach without dynamically -**If I pass the static value to start row and end row i.e 1 and 10 ,it will fetch the 10 rows from api and load the data in destination using copy activity only .

Second approach with dynamically -

Using web activity pass the count api which gives the count of data .
2.Create string type variable under the pipeline.Using set variable activity get the output of web activity count and store into string type variable using @ hide (activity('Web1').output.count)
3.Using copy activity in source under the request body section we need to pass the above variable in end row parameter that dynamically fetch the count of api and load the data.

This 3 rd point is creating problem ,actually it's not giving any error while execution but also not loading the data in destination .
In request body we pass the default value as in Json format like below;-

For dynamically how we can pass the variable under the json.I tried below way

@{replace('{"start_row":1,
"end_row":0000,
"email_id":"ABC",
"security_pin":"BCD",
"cin_login_code":"EFG"
}','0000',variables('end_range'))}

I am able to see the output in execution history "end row" will get the value but still not loaded the data in destination.

It would be great if you can help me out in this problem .
Please let me know if you require any more information

Answer

Hello @Meghana Nikam and welcome to Microsoft Q&A.

If I understand correctly, those 5 properties need to be passed in request body, not headers. Also, of those 5, only start_row and end_row change. You said you know how many rows total there are (the counts)?
You were also able to make it copy data with the REST dataset, but only fixed, not dynamic.

I think the solution is to put the Copy activity inside a loop. The loop will make the body and pass to Copy activity. The Copy activity uses the REST dataset from your first example.

First we need to know how many pages.

Let us define some variables:

api_row_count : the count of records you got from API
count_per_page : how many records you want in each request
total_pages : how many pages total
page_array : an array type variable we will store page numbers in

total_pages = ceiling( api_row_count / count_per_page )

@string(  
if(  
  greater(  
      div(float(variables('api_row_count')),float(variables('count_per_page'))),  
      div(int(variables('api_row_count')),int(variables('count_per_page')))),  
  add(1,div(int(variables('api_row_count')),int(variables('count_per_page')))),  
  div(int(variables('api_row_count')),int(variables('count_per_page'))))  
)

page_array = range(0,total_pages) => [0,1,2,3,4...]

@range(0,int(variables('total_pages')))

Then we pass page_array to be items in a forEach loop. Inside the forEach loop, we have the copy activity using the REST dataset. In the body we can have

start_row = (current_page * count_per_page) + 1
end_row = ( 1 + current_page) * count_per_page

{  
"start_row" : @{add(1,mul(item(),int(variables('count_per_page'))}  
, "end_row" : @{ mul( item(), add(1, int(variables('count_per_page'))))}  
, "email_id" : "xxxx"  
, "security_pin" : "xxxx"  
, "cin_login_code": "xxxx"  
}

item() is the current value the loop is on. It is taken from the iteration over page_array.

Does this help?

Share via

Fetch the data from Rest APi and load into Postgresql table dynamically using azure data factory

1 answer

Your answer