Can you disable batching concurrency?

[rwitsmc]

Hi,

Have the following situation...

A CSV file with approx 2500 rows is dropped into Box.

A Workato recipe then processes the rows, creating new items in a SharePoint Online list.

SharePoint Online appears to have some kind of "flood protection" in that it will terminate the connection if it receives too many requests in too short space a time.

To try and counteract the flood protection I've set the recipe to update SharePoint Online in batches of 200 records, and then apply a 10 minute pause before it sends the next batch.

The problem is that because the CSV has 2500 lines Workato will attempt to split the job into multiple concurrent jobs, which of course triggers SharePoint Online to drop the connections. Concurrency is set to 1, so I'm not sure why Workato is doing this - maybe the concurrency doesn't apply to batching.

I've tried setting the job Batch Size to 5000 to prevent this, but Workato ignores this number. Looks to me that the maximum allowed is 2000, as it will always kick off a minimum of 2 concurrent jobs with a high Batch size. If i reduce the Batch size to 1000 then it starts 3 concurrent jobs (which is what I'd expect).

 Is there any  way I can prevent Workato from splitting the job into concurrent batch jobs?

If not, how can I handle this situation to prevent the connection dropping issue due to too many requests?

Thanks
Martin

[rwitsmc · Accepted solution]

Thanks mppowe, I was thinking along the same lines.

So what I did to get this working was to ditch the "Box: New/updated CSV" connector which batches, and instead use the "Box: New/updated file" connector which doesn't.

Unfortunately this connector doesn't do any parsing, so I had to add some extra actions to get the contents of the CSV and parse it, and I had to re-map all the columns, but it did what I needed - a single job to process the entire file.

I still had the SharePoint Online connector drops due to the amount of API calls in a certain amount of time, so I then had to implement my own pseudo batching within the recipe, creating a "if (listIndex + 1)%500 == 0 then wait 5 minutes" action to pause the recipe every 500 items processed, the effect of which was to artificially produce sequential batching as opposed to simultaneous batching.

And it works! 🙂

[mppowe]

I haven't worked with the Sharepoint connector, but perhaps you could trigger off the file instead of the rows?  Or if all else fails you could use Pub/Sub connector and as the rows come into Workato you publish them, then your recipe that consumes can better manage the batches and overall orchestration.  Or even dumping the rows of the CSV into a Lookup table (max 10,000 rows) and then a recipe that runs on a schedule to pull from there.

Hope that helps some.

[rwitsmc · Accepted solution]

Thanks mppowe, I was thinking along the same lines.

So what I did to get this working was to ditch the "Box: New/updated CSV" connector which batches, and instead use the "Box: New/updated file" connector which doesn't.

Unfortunately this connector doesn't do any parsing, so I had to add some extra actions to get the contents of the CSV and parse it, and I had to re-map all the columns, but it did what I needed - a single job to process the entire file.

I still had the SharePoint Online connector drops due to the amount of API calls in a certain amount of time, so I then had to implement my own pseudo batching within the recipe, creating a "if (listIndex + 1)%500 == 0 then wait 5 minutes" action to pause the recipe every 500 items processed, the effect of which was to artificially produce sequential batching as opposed to simultaneous batching.

And it works! 🙂

[mppowe]

Glad to hear it!  Though I wonder if instead of having to do that formula around the index, I think the Repeat action could be used, and specify it to repeat in batches, say 500 in your case.

Maybe for next time

[steven-marissen · Accepted solution]

Just throwing Ideas out there, but here is mine:

1. Have the main recipe that processes the csv form Box at 2000 records per batch (no limitations needed)
2. Have that same recipe call a function (asynchr) within a repeat and pass 200 records each time to it
3. Have the function at concurrency 1 + create a batch create of 200 on Sharepoint + add a wait for x amount of time afterwards.

I believe that should help it process job per job with 200 records at a desired pace you can determine yourself.