How to execute a stored proc once after processing EDI file in BizTalk?

Question

This is more of a general architecture question; I have a few ideas but I figure we can't be the only ones who have ever needed to do this.

When processing a HIPAA EDI file through BizTalk (say an 837 healthcare claim), it is split in multiple claim files, and any orchestrations it goes through are run simultaneously (well, practically). I need to execute a stored procedure just once after every claim has been stored to the database, so the proc obviously can't execute from the same orchestration that saves the data.

Suggestions? Thanks!

Bill

Answer 1

I can think of a few options but I don't know how to guarantee you process all of the documents without either processing the document sequentially or inspecting it first to know the number of claims. A few options:

1. Rather than letting the pipeline do the splitting you could do the splitting in an orchestration and process them sequentially instead of in parallel.
2. Again have a single orchestration do the splitting but before beginning the splitting count the number of documents and have a separate process that is polling for that number of documents to be committed to the database. This would allow you to at least fire off all of the sub documents asynchronously.
3. Create a pipeline component that inspects the document before allowing the EDI component to do the splitting and similar to above have a separate process polling for all the claims to complete.

Seems that the biggest issue here is what do you do if one of the claims fails, you'll need some roll back strategy if you only want to commit when the full file completes.