Problem with Workflow Using Data Sources

[jlaps]

I am trying to make sense of a problem we are seeing on our offboarding workflow in regards to a data source being used to export data to a file on a mid server for "other things" to happen. This data source is activated, export set run through it to create a CSV, and then exported to a mid server. The workflow then waits for 45 minutes, checks for a response in the form of a new file being dropped on the mid server, and the workflow continues.

This works when firing off one at a time without issue.

The problem we are seeing is that the workflow SAYS it succeeds in exporting the file, and then the response failure 45 minutes later creates a manual task where this automation is failing. The issue being, that the export does not happen even though the workflow says it did. This only seems to occur on Friday nights, when our HR system initiates multiple offboards at the same time (seconds apart in the workflow instances). My theory is that the data source is "in use" already when another workflow tries to access it to attach the export set CSV... the workflow says it worked, but the data source was already in use, so it did not actually work.

Does that make sense or is there perhaps a better theory on why this only seems to fail when multiple offboards are occurring?

If that does make sense, what is the best way to fix it? If I were to instead add an entry to a table with the info to be exported, and had a scheduled job do the exporting via data source? Would that prevent the same data source colliding like I suspect is happening? 
Hoping for some insight.

Thanks!

[Mohammad Danis1]

Hi @jlaps ,

I think you are right.

It's failing because each time it tries to access the same data source → same export set → same MID Server.
If the mid server is slower to respond or if the disk I/O is already busy, subsequent workflow could report “success” prematurely, or fail silently if the underlying process hangs.
To avoid this situation can't you try using staging table instead of directly exporting and then use a scheduled job to export data from this table every few minutes.

Regards,
Mohammad Danish

[jlaps]

That is what I am thinking. My concern though, is that if I have say a business rule watching for new entries on this table, and when it sees one, it does the data source and all the  things. If another entry to the table comes in 2 seconds later, and the rule fires again... will it wait while its working the first one, or will it also collide with the first request?

To your point on a scheduled job, that is what I am leaning towards, so long as the export file can handle multiple entries which I am confirming on the far end if they can handle that.

My concern being that no matter how I trigger this, how do I make it go sequential without stomping all over itself if its already running another job?

[jlaps]

No multiple entries on this process it turns out, one-one only. Thinking I may have to have a 'status' field on the table to step through these one at a time? Have a rule that watches for changing of the status to 'complete' to check for another in the table that is ready to process... hmm.

Is there something clever I am not considering?

[Mohammad Danis1]

@jlaps , Umm.. that would be smart approach. 

Regards,
Mohammad Danish