A dude’s guide to BizTalk debatching: Flat File, SQL, Oracle and other transactions
Are you going to a BizTalk Debatching party and want to show off your skills?
Well you’re in luck! In this post, I will show you how to debatch Flat Files, SQL, Oracle and other transactions, using BizTalk to impress like a boss. Keep in mind that the use of Custom Pipeline Components would be cheating, as it would be like going to a club with Joey Tribbiani as your wing man.
Why debatch?
Throughout the years, we’ve all been the victims of large set of records that have forced us to create complicated loops, ending up with algorithms that you’re better off keeping it on the DL until you’re out the door. Let’s review some of these reasons:
Too complicated maps
To avoid complicated algorithms, loops and unnecessary artifacts. We all agree that simplicity is one of the keys to successfully algorithm design.
Target System Expects One Transaction at a time
Your target system handles one transaction at a time. In this scenario, you’re forced to push one transaction at a time to the destination system.
Avoid Orchestrations
You want to avoid the use of Orchestrations, or you want to simplify your BizTalk debatching process (avoid XPath and exceptions).
Minimize pulling calls
You want to minimize connections to the Database. A lot of people like to use the “Select Top 1” type SQL Statement and set the pullWhileDataFound setting on the adapter, but as we know this is resource intensive to both the BizTalk Server as well as the SQL Server. Ideally you want to make one call to the database and extract all available records to be processed at once.
Possible Side Effects of BizTalk Debatching
Once you’ve made the decision to implement BizTalk Debatching
Execution Order
Sometimes the order in which transactions reach the destination matters. For instance, transactions that depend on each other. Once the parent transaction gets debatched, the children are dropped in the Message Box in no particular order, making reaching the destination in whatever random order.
Flooding the Message Box
I remember one of my first projects, I was proud to have figured out how to debatch these huge complicated transactions, however, I forgot to inquire about volume, leading to flooding the system, taking over the server and slowing down other transactions.
All or none
In a lot of cases, you want “all or none” child transactions go through. Meaning, if one of them fails, you want all of them to roll back. When you implement debatching you lose control over the transactions and the relationship between them, therefore not knowing if one of them has failed, to be able to roll back the rest.
In our next post, we will reveal how to go about performing these BizTalkdebatching methods one by one. Until then, good luck!