To show you the approach in a quick way, let's write some codes to replicate the scenario I demonstrated in this post. Firstly I created below function:
private static string callAPI(HttpClient client, string key, string endPoint)
{
string jsonBody;
var header = new MediaTypeWithQualityHeaderValue("application/json");
client.DefaultRequestHeaders.Accept.Add(header);
client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Key", key);
HttpResponseMessage response = client.GetAsync(endPoint).Result;
jsonBody = response.Content.ReadAsStringAsync().Result;
return jsonBody;
}
Clearly, this function will extract response body from the Rest API (here we assume the API uses HTTP header "Key" for authorization purpose).
So now we have the base method to call the API, what we need to do is creating a recursive routine, so that we can read the next page until nothing returns:
bool tryNextPage = true;
using (HttpClient client = new HttpClient())
{
while (tryNextPage)
{
string jBody = callAPI(client, key, endPoint);
if (jBody.Length > 50)
{
ResponseBuffer.AddRow();
ResponseBuffer.JsonBody.AddBlobData(System.Text.Encoding.UTF8.GetBytes(jBody));
int page = int.Parse(endPoint.Substring(endPoint.Length - 1, 1));
endPoint = endPoint.Substring(0, endPoint.Length - 1) + (page + incremental).ToString();
}
else
{
tryNextPage = false;
}
}
}
As you can see, I defined a Boolean variable to control the While loop. And in each loop, I read length of the Json response to determine if we should read next page.
Simple approach, isn't it? Though it looks like we have to write more lines of code when compare to power query, SSIS does have its own advantages, such as data quality control. Until my next post, enjoy the pagination.
