Pagination rules in Copy Activity for JIRA data

[Nur]

Hi Fabric aficionado,

I want to retrieve data from JIRA using Copy Activity in Microsoft Fabric and save it in a lakehouse. I encountered pagination problem because maximum result JIRA can give per page is 100 but I have around 600 rows. Below is some detail on the configuration:

Relative URL: /rest/api/3/search?jql=project=KEP&maxResults=100&startAt=0

AbsoluteUrl: /rest/api/3/search?jql=project=KEP&maxResults=100&startAt={0}

I tried using pagination rules in copy activity e.g. AbsoluteUrl (see screenshot) but it only gave me redundant result in a json file (only the first 100 result 4 times) (see screenshot)... So, what did I do wrong here? Is it better to save the output file as one json file or one json file per one page? 

The expected result should be something like:

"startAt":0,

"startAt":100,

"startAt":200 and so on

Thank you for your time.

Kind regards,

Nur

[Nur]

Hi again @justinjbird,

Now, my goal is to have one json file per page. Because when I saved it all in one json file, it is a huge file... So, I was thinking to save one json file per page. Maybe I can use dynamic content for that? Do you have any idea how to do that?

Kind regards,

Nur

[justinjbird]

I think you would have to do something programatic so that you could dynamically set the file name via a loop. So perhaps a foreach that increments the start number and that start and end numbers and the start number is added to the file name for example. You would have to come up with a way to know when to break the loop too.

Just consider that you are running multiple activities at that point vs one though. Ok the file is big this time, but if you can apply filters to the api to only return modified items in the future then maybe that's an acceptable tradeoff and you just have one big file initially that needs processing then increments in future.

[justinjbird · Accepted solution]

Hey @Nur based on the pagination rules described here you need to map offset into the relativeUrl and the pagination rules aren't quite right. I have used this api to demonstrate it https://dummyjson.com/products?limit=50&skip=0.

In the demo url, the skip filter is used to get the next set, I would set up with relativeURL as ?limit=50&skip={offset} and for absoluteURL in the pagination rules set the value to {offset}. Next to it you should select range which will give you three values - start, end, offset. Offset will determine how many rows in each set, and probably you want start to be from 0. Adding a value to end will mean a fixed number of rows returned so you may want to populate that or you may want to simply leave it blank.

The only other thing you need to consider is your end condition - if you don't set a fixed end value, what condition determines that you don't need to continue looping? That's where you will want to determine an EndCondition. There are many examples given on the page but essentially the condition focuses on whether an element exists / doesn't exist, whether a value is constant or whether an element is eventually empty. So for this api I set the condition to $.products needing to be empty. Here's the finished configuration:

I ended up with 5 rows in the file, the final row being an empty one:

So maybe I could adjust the endCondition to eliminate that row, but hopefully I've given you enough info for you to proceed with what you're trying to do...

[Nur]

Hiii @justinjbird,

Thank you for your reply and answer! I adjust accordingly and it works! 😄

I used the following configuration:

RelativeUrl: /rest/api/3/search?jql=project=KEP&maxResults=100&startAt={0}
QueryParameter:{offset}:Range:0:<empty>:100 

(Since it is JIRA data, parameters like limit and skip are not available, but instead they have something similar like maxResults and startAt :))