In a normalized database environment, a single canonical data entity often has its data spread across more than one underlying database table. Therefore it's critical to be able to insert data into all relevant tables from within a single transaction. Otherwise, we end up with an inconsistent state across our tables. Ignoring the database-centric ways to ease this burden (such as table views, stored procedures), let's consider how we would update multiple tables at once from BizTalk Server 2009. Specifically, we want to take a material batch canonical message and insert its data into multiple tables. After that, we need to execute a scalar operation against the database in order to find out if any shipping trucks are regionally available for transporting this batch.
First, we need the XML schemas which represent our data source. By installing the WCF LOB Adapter SDK, we get a nice metadata browser built into Visual Studio.NET. We access this from a BizTalk...