Rest api call with multiple pages

Hi AstridM 

 

Here is an example how to do this using power query, which I got from copilot.

 

let
    GetPage = (page as number) =>
    let
        Source = Json.Document(Web.Contents("https://api.example.com/data?page=" & Number.ToText(page))),
        Data = Source[data],
        NextPage = if List.IsEmpty(Data) then null else page + 1
    in
        [Data = Data, NextPage = NextPage]
in
    GetPage

 

let
    GetAllData = (page as number, accumulated as list) =>
    let
        Result = GetPage(page),
        NewAccumulated = List.Combine({accumulated, Result[Data]}),
        NextPage = Result[NextPage]
    in
        if NextPage = null then NewAccumulated else @GetAllData(NextPage, NewAccumulated)
in
    GetAllData

let
    AllData = GetAllData(1, {})
in
    AllData

Hi AstridM,

Thank you for reaching out to the Microsoft Fabric Community Forum.

After reviewing the details you provided, here are a few workarounds that might resolve the issue. Please follow the steps below.

Yes, it is possible to migrate an SQL Server Integration Services package that makes API calls and returns multiple pages. 

 

• Please ensure that the API endpoint returns the expected data. Utilize tools like postman or curl to manually test the API calls and verify the response. Confirm that the response includes necessary pagination metadata.

• APIs supporting pagination include metadata in the response headers or body, such as total_pages, current_page, or next_page. If this metadata is absent, consult the API documentation or contact the API provider for pagination details.

• If the API lacks pagination metadata, implement custom pagination logic in your SSIS package. Here’s a general approach: Make the initial API call to retrieve the first page of data. Use a ForEach Loop container in SSIS to iterate through the pages, tracking the current page number with a variable.

• After each API call, check if the response contains data. If the response is empty or contains only null values, assume there are no more pages to fetch.

• Set up an HTTP Connection Manager to connect to the API endpoint. Use a Script Task to make the API calls and manage the pagination logic, writing the script in C# or VB.NET. Configure the ForEach Loop container to iterate through the pages, updating an SSIS variable with the current page number after each iteration.

Please go through the following link for more information about dynamic parameters:
Foreach Loop Container - SQL Server Integration Services (SSIS) | Microsoft Learn

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

 

Best Regards.