BizTalk Sending Updated Version of Message to SOAP Recipients

What happens to downstream SOAP recipients if the message sent from BizTalk is a different “version” than the original?

Let’s assume I have an enterprise schema that represents my company’s employees (e.g. “Workforce”). BizTalk receives this object from SAP and fans it out to a variety of downstream systems (via SOAP). Because direct messaging […]

Inserting into multiple tables with multiple Pk/Fk relationships

Inserting into multiple tables with multiple Pk/Fk relationships

I recently was working for a company who needed to store data coming in from the 837 claim into a database.

Experiencing many poorly designed databases to store claim data before, I create a database structure that will capture what the client was looking for, but also allow easy implementation of additional values to be captured into the database as use of this process increased. Now my goal/issue was to get BizTalk to take the 837 data and insert all of the data into the various tables.

For the purposes of this blog, I have simplified the example, but the concepts are the same.

The first thing I have done is created a psuedo837Claim schema, that looks somewhat like the 837 schema looks like. In the picture, I have marked what is repeatable.

The next thing I have done is created the database structure:

So now I need to start generating the schemas that represented the web I have just created. We will start by following Steve Kaufman’s guidance hereand creating an at-identity schema that we will use throughout the process. Once I have completed that, I generate the Header schema from the Header table. I select all of the columns (as I will for all but the end, but we will get to that later).

Then for the purposes of this blog, I will import the Application table. Here comes the modifcations. Once I have imported the Application table, I need to import the at-identity schema, and I also need to make the Application record a Complex Type for later use. Because the prefix for the namespace is tns, the Data Structure Type needs to be tns:Application

I then add the primary key field and set the Data Type to the at-identity

After saving it, I then go back to my Header schema and import both the at-identity and the application schema

I then add a new record as a sibling to Header, and I name the new record Application (the name of the table), I choose as the Data Structure Type ns0:Application (ComplexType) and set the Max Occurs as blank, as we will not be repeating until later

I complete the process for all of the other tables, except for the tables that do not have children, and there I don’t need to select the primary key field (as I will let SQLXML do that without me coding for that)

So I generate the schemas for everything and now we are getting to childless table importing,ProviderInfo for example, I don’t need to add the primary key, I simply need to include the foreign key when I generate the schema

Remember that all I have to do is define the Data Structure Type as tns:ProviderInfo

When I import the InsertProviderInfoService into the InsertHeaderService schema I choose ns2:ProviderInfo and again, since it is not repeating, you can simply leave the Max and Min Occurs blank

You continue this same pattern, adding primary keys if there are children to the table, omitting them if not.

The final output should look like this:

Delete all of the Orchestrations excepth the first one that has defined the Header and rename it to InsertClaimProcess and change the Typename to InsertClaimService also

Now for the mapping (finally). Create the map with the EDI input, and the header schema output. Since the Header, Application, and provider are all single instances, I hard code a, b, and c respectively into the updg:at-identity primary key and the foreign keys. For the ProviderInfo, ProviderLocation, and Subscriber, I link them all to ‘c’

Since Subscriber is the first repeatable child table, I add an index from the source to the d primary key and then distribute the concatonated foreign key thru the child tables

Finishing out the relationships look like this:

Now all I have to do is finish the mapping and I am complete, invoke the map in the orchestration and it is complete.

Hope that this was as fun to do as it was to discover!

Jon Flanders coming to Australia to deliver Applied WF, another of the Readify Industrial Strength Series

Jon Flanders, one of myBizTalk Server MVP colleagues is coming to Australian in November to deliver another in the ongoing Readify Industrial Strength Series, Applied Windows Workflow Foundation. To get the detail and to enrol visit Having heard Jon speak several times I can ensure you that you will come away from the course eager to dive into your next WF project.

Jon Flanders coming to Australia to deliver Applied WF, another of the Readify Industrial Strength Series

Jon Flanders, one of myBizTalk Server MVP colleagues is coming to Australian in November to deliver another in the ongoing Readify Industrial Strength Series, Applied Windows Workflow Foundation. To get the detail and to enrol visit Having heard Jon speak several times I can ensure you that you will come away from the course eager to dive into your next WF project.

BizTalk RFID: notes

BizTalk RFID: notes

After finally getting around to setting up VMWare Workstation with a BizTalk 2006 R2 Beta2 + BizTalk RFID, I did several experiences with the Phigets RFID reader and the Phidgets DSPI.

Two notes about the setup of the DSPI: first, do it manually, following the instructions. The setup script has an error in the names of the dll files (is uses the name “phidgets” instead of the correct name of the dll, “*phidget*”). Second, if you have an error when starting the provider, check this page in the Msdn forums. The setup guide for the Phidgets RFID quickly guides you through a complete configuration of the provider, device, and business process, so I highly recommend you follow it.

One important limitation of the Phidgets DSPI is that it doesn’t support the elimination of repeated reads. Just to give you an example of why this is a limitation, if you hover the Rfid tag for about half a second over the reader, you get 10-20 tag reads. I have my doubts as to wether this elimination should happen at the provider level (DSPI)… it seems to me it should be done in the generic RFID Infrastructure. Apparently other DSPIs have the same limitation, but there’s a way around it: either use specific features of the device (non applicable in the case of the Phidgets reader), or an event handler associated to the business process you define (check the DupElim sample in the BizTalk 2006 R2 Beta2 samples).

Something that I found unintuitive when setting up BizTalk RFID is that this package (much like BizTalk Services) is not part of BizTalk Server itself. Or rather, what I mean is: you won’t get “tag read” messages directly into the Message Box, like you could assume you’d get. Rather, you associate some kind of “sink” to an application/business process you configure in the RFID management console (typically, the out-of-the-box SqlSink that just drops the reads in a SQL Server database), and then use BizTalk Server to consume events from that sink. An alternative way of getting tag reads is to use an orchestration to consume the RFID webservice (check the ConsumeRFIDWS sample). This does make BizTalk RFID into a generic “RFID server”, but the BizTalk in the name is misleading. One further note about this is that you do get, out of the box, the capability to use the Business Rule Engine as a way of validating your tag reads (check the BRESample).

Microsoft’s forum for BizTalk RFID is here, and I highly recommend it if you have problems, I found a solution there for all the difficulties I had.


On to a different subject, Microsoft has recently updated the Roadmap for BizTalk Server, with the interesting part being the “Beyond Biztalk Server R2” section. It seems that the “Solution Designer” that left us open eyed when it was shown in a Channel9 video two years ago is really not going to happen.

A Series of Fortunate Events

A Series of Fortunate Events

August really is event month, with 7 events planned that our team are involved in!

So what’s on the calendar?

1. Tech.Ed, if you are not going to make it to the event this year I am going to be tasked with providing vidcasts throughout the event so that you can follow along on our event site!

2. I have organised for Lou Carbone to do a “dress rehearsal” for the Tech.Ed Keynote at a special UPA meeting at Microsoft on Thursday the 9th August. Warning… I don’t recommend going to this meeting if you are lucky enough to be going to Tech.Ed.

3. Kirk Jackson has organised a NZ.NET Dev Code Camp for the Sunday before Tech.Ed. Registration, and further details can be found at

4. Darryl has organised an inaugural Women in Technology Dinner as part of Tech.Ed on Monday 13 August from 8.00-10.00pm at the SkyCity Convention Centre.

The dinner is designed to recognise the outstanding community of women who work in the developer and IT fields in New Zealand.

The dinner is hosted by Helen Robinson, Microsoft New Zealand’s Managing Director and will also feature a panel of female industry influential’s including Eileen Brown from Microsoft UK as keynote speaker.

Whether you are attending Tech Ed or not, you are welcome to join this fun and informative event, take part in the panel discussions and network with your industry peers.

To register call (09) 309 2440 or email . Tickets are free to Tech Ed attendees, or $80 +GST for non-Tech Ed attendees.

5. Paul and Ryan have been organising Student Day 2007

Auckland – Sky City Theatre – Thursday 16th August – 10am – 6pm

Wellington – Massey University – Museum Building – Friday 7th September – 1pm – 6pm

Christchurch – University of Canterbury – S1 – Friday 14th September – 1pm – 6pm

Student Day is the premier IT student event day offering students an interactive insight into the IT industry. For more details and registration check out the website.

6. We have chosen to sponsor the “Design is King” Auckland Web User Group meeting on Thursday the 16th August 6:30pm, details here.

7. We are sponsoring and presenting at Web On the Piste in Queenstown. The event organisers have asked me to share this…

Web on the Piste is the ultimate conference for Rich Internet Technologies (RIA) in Australia & New Zealand. Held in a very cool part of southern New Zealand called Queenstown on the 22nd – 23rd of August, it’s aimed squarely at web developers & designers, business implementers & anyone who is interested in learning about RIA’s and how they can leverage them in today’s market.

Web on the Piste is 2 days of sessions on RIA’s where you learn about the latest technologies, hear how they are being used and how to benefit from them. With an international cast of speakers and speakers from Microsoft and Adobe, the event is a must do for anyone working in the Internet technology space.

For more information on the agenda, the venue and what’s included, please visit

WOW! tomorrow we are off to an offsite at the Grand Chateau, so I’m sure I’ll see a lot of you in August at the various events!