Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mark_carlisle
Advocate IV
Advocate IV

Copy Data Activity for JSON from paged API response

I have setup a data pipeline that successful retrives data from an API and loads the JSON response into a lakehouse.

 

My issue is how the copy data activity saves a paged response to a single JSON file, like this:

 

{
    "results": [REMOVED],
    "resultsTotal": 255,
    "resultsPage": 0,
    "resultsPageSize": 100,
    "nextCursor": "AQ=AA=AYrsQAgw=MTk5OTIzMDYyNyE1M2E0NWRjYjNhNDUyMjc1NGE5MjgwOTY4NWFiOTMwMw",
    "startDate": "2023-10-01T00:00:00.000+0000",
    "endDate": "2023-10-02T00:00:00.000+0000"
}
{
    "results": [REMOVED],
    "resultsTotal": 255,
    "resultsPage": 1,
    "resultsPageSize": 100,
    "prevCursor": "AQ=AQ=AYrsP7JA=MTk5OTIzMDYyNyE0ZjhhYmY1N2U2MzM5YTFiYjA5OWNhZjE4ZTY4NDdlNA",
    "nextCursor": "AQ=AA=AYrqPFiQ=MTk5OTIzMDYyNyFjZjFjZmIyN2E1NWExYTk1NDJiYWYyMmIxNzczNjJlNg",
    "startDate": "2023-10-01T00:00:00.000+0000",
    "endDate": "2023-10-02T00:00:00.000+0000"
}
{
    "results": [REMOVED],
    "resultsTotal": 255,
    "resultsPage": 2,
    "resultsPageSize": 100,
    "prevCursor": "AQ=AQ=AYrqLxIg=MTk5OTIzMDYyNyFkYzNjNzczY2Q0Njg3Yjc1NTUzZGJhNjRiMzIwZDg3MQ",
    "startDate": "2023-10-01T00:00:00.000+0000",
    "endDate": "2023-10-02T00:00:00.000+0000"
}

 

This is an example of a response that required paging and what Fabric loaded into the Lakehouse, note the lack of commas between the response, invalidating the JSON.

 

Is this a limitation of the activity or have I got it configured incorrectly?

mark_carlisle_0-1715362798130.png

 

1 ACCEPTED SOLUTION
Nero
Frequent Visitor

Try setting File Pattern to "Array of Objects"

Nero_0-1715545942525.png

 

View solution in original post

3 REPLIES 3
Nero
Frequent Visitor

Try setting File Pattern to "Array of Objects"

Nero_0-1715545942525.png

 

Hi,

 

Thanks for your reponse. Managed to figure this out over the weekend. Looks like I'm impacted by a bug of some sort because we'd been switching that setting about and still getting the same output. It would seem that this bug is preventing the setting taking effect:

mark_carlisle_0-1715587970410.png

 

I was able to overcome this by editing the JSON (Edit Fabric Pipeline's JSON (linkedin.com)) manually.

Hi @mark_carlisle 

 

Thanks for using Microsoft Fabric Community.

Glad that you were able to find some insights and thank you for sharing the same with the community as it can be helpful to others.
Please continue using Fabric Community for further queries.

 

Thank you.

Helpful resources

Announcements
FabricCarousel_June2024

Fabric Monthly Update - June 2024

Check out the June 2024 Fabric update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.