Pre-Dev BizTalk Mapping Documentation

I have been working with BizTalk for a long time. Like many, I came from the world of “let’s jump in and get this thing done.” (aka Cowboy Coding or Get ‘er Dun!)

That has served me well and continues to do so. However, as I work on more large, enterprise-scale, cross-functional integration projects, the need for the D word increases – Documentation (developers shudder!).

I am not one for writing reams of documentation, as are most developers. I prefer to focus on diagrams, well-written code, and a brief configuration document (endpoints).

However, one document that has become a staple is an easy-to-read mapping document. I have gotten into the habit of creating an Excel spreadsheet for each of the major maps in an integration. I use it heavily during the tedious process of field-mapping discovery. I define the source and target schema, put the target schema into a spreadsheet, and then use it as a guide to define the field mappings with subject matter experts. If possible, I have a projector and sit with clients to complete the document with them. Screen-sharing with Lync or GoToMeeting works quite well also.

Sample mapping document (simplified for brevity, not a real-world example).


A few points on the document…

  • Pseudo-code and pseudo-xpath expressions describe source field mappings and logic
  • Dark blue signifies groups of records
  • Light blue signifies a record element
  • White rows are field elements or attributes
  • Yellow highlights show things in need of follow up.
  • Red highlights would show if something were a critical issue.
  • The date on the left is the last time an issue was discussed.
  • This sample shows an element-centric PO being mapped to an attribute-centric Invoice.

I have found these documents to be extremely valuable, especially when questions arise as to “what comes from where and why?” Rather than having to crack open the code, or ask a developer, business folks can reference these documents. On some occasions, I have been able to hand a document stubbed out with the “To” schema to a Business Analyst and have them make a first pass at the field mappings. Then we would review it and use it in the map development cycle.

Other random thoughts on mapping…

Having done this, and used this process for a long time, I have some tips and opinions.

  • The document is complete, or near complete BEFORE developing the BizTalk map. If field-mapping discovery drags on, I will go ahead and map what is in the document up to now. This is when the dates in column A come in really handy.
  • I never do mapping sessions with a client using the BizTalk mapper itself. I want to train the client to use these documents themselves. Otherwise they will always depend upon a developer to crack open BizTalk to answer questions about field mappings. I want to train them to answer their own questions using these documents.
  • I have tried using the BizTalk Map Documenter, and I like the tool. However, it is a post-development tool. In very complex scenarios, up-front analysis has saved me a lot of time.
  • Clients I have worked with love these documents, both because they have a stake in completing them, and they are a durable non-code artifact they can understand and refer to.

The Three-Question Architectural Design Session (ADS)

I recently participated in an Architectural Design Session (ADS) with a client centered around their use of BizTalk Server. They are upgrading their environment and wanted to make sure they weren’t setting themselves up for failure. These types of engagements can be a lot of fun. But, they also can be a little intimidating going in. Having a roadmap of what the engagement looks like ahead of time really helps.

Our sales/solutions analyst who kicked off the engagement used to work with Microsoft. He shared with me three questions that he always had going into ADS meetings that were very helpful.

  1. Where are we today?
  2. Where do we want to be?
  3. What are the gaps from here to there?

With these three high-level questions in mind, I can interview a client about their current processes, architecture, code, configuration, deployment, documentation, practices, hardware, software, etc. From there we can move on to future-oriented questions in those same areas. That allows a simple gap analysis between the two.

The result of the ADS is a document that essentially outlines the answers given to these three questions. The gaps section can be extended to propose solutions to bridging current-state and future-state. That is the stuff a project is made of!

How I Passed the BizTalk 2010 Certification

Yesterday, I passed the BizTalk Server 2010 certification exam. I had heard that previous versions of the exam were quite difficult because BizTalk is a broad tool. I have only taken one Microsoft certification exam in the past, and failed it. So, I was rather nervous going in. However, I have been working with BizTalk Server for quite a long time, and was much more confident with this tool than the other exam’s focus area (.NET data access).

Here’s how I studied…

I have actually used the presentations and labs in this kit to deliver BizTalk training to clients. It is very good. In November, I presented a client training where we went through all but four of the modules in the training kit. That was what put getting the certification onto my radar. Like most of us, I have extensive use of certain pieces of BizTalk, but there are other components that I do not get to use that often. This training opportunity really helped to round out my knowledge. And of course, teaching is a great way to learn!

Thanks to Kent Weare and cohorts for putting this great guide together! I can say with the exam fresh in my memory that much of what the exam covered was in this book.

After I decided to go for the certification, I looked around and found this book. It is a quite concise book on the subject. And just over 300 pages, and lots of screen-shots, I was able to plow through it in a couple of weeks. That mainly happened over the holidays which helped because I had some extra time.

  • Targeted personal lab-time

I knew the areas that I was not as experienced with. As such, I set aside time to get into the tools and kick the tires. I prototyped those weird direct binding scenarios that aren’t often used (partner ports, self-correlated) and exception compensation. I prototyped using role links (not my favorite pattern for party resolution by the way). I prototyped both consuming and publishing WCF services.

Like you may have heard in the past, multiple modes of input can really increase your chances of passing (instructor-led [even when you are the instructor!], books, lab time). While I have a long history with BizTalk, and may have passed the test, I wanted to do whatever possible to make sure I did not fail it, particularly since I had failed an exam in the past.

Writing Beautiful Stored Procedures

At Innovative Architects, we are very blessed to have both of the Data Chix on our team. Audrey Hammonds (@dataaudrey), half of the aforementioned Data Chix presented a lunch and learn today called. “Writing Beautiful Stored Procedures.”

Without being too soap-boxy, she gave some great basic tips on formatting stored procs. One tool she shared was an onlineT-SQL reformatter. Check it out!

Note on BizTalk Compensation

I was goofing around with Compensation blocks, as I prepare for the BizTalk 2010 Certification exam. I expect to get one or two questions about Compensations, so I at least wanted to play around with them some. I use scopes for exception handling all the time. However, compensation isn’t something I have really implemented.

That being said, I was having a little bit of trouble actually getting a Compensation block to fire. Well, it was for one simple reason – like “smack your forehead simple.” Fortunately, the wonderful interwebs contained the answer.

  • The Compensation section for a scope gets activated only when the scope terminates normally or the scope completes its execution.

I was trying to stack a compensation block underneath an exception block. That use case violates this rule. In order to compensate, I had to create nested scopes, such that the inner scope transaction completed, and the exception occurs afterward (I purposely caused a div by zero error). Then the outer scope’s exception handler calls the compensation block of the inner transaction scope.

This article (from way back in 2006!) has great screen shots and useful information about compensation in BizTalk.

Happy Orchestrating!

Videos – BizTalk Integration Summit 2013

I had a little bit of a hard time finding these on Channel9, so I figured it would be worth sharing these excellent videos from the BizTalk Integration Summit 2013 here. These are the sessions dealing with Windows Azure BizTalk Services.
Microsoft BizTalk / Integration Summit 2013 (November 21-22) included over 250 customers, partners, and Microsoft SMEs focused on Integration solutions. With BizTalk Server 2013 on premise capabilities, Windows Azure BizTalk Services and Service Bus in the cloud, and elite Integration Partners around the world – we are transforming the way businesses drive B2B, EDI, Supply Chain, and Application Integration.

BizTalk Administrator’s Checklist

BizTalk Administrator’s Checklist

Here is a great checklist from Microsoft’s BizTalk Support team for regular production monitoring. As I reviewed the checklist, I couldn’t help but think that BizTalk360 simplifies some of these tasks by bringing the tools together into a single user interface; particularly in a multi-node BizTalk Group/Cluster.

Save Whole XML File in SQL Table Field

I worked on what ended up being a surprisingly interesting prototype to answer a question in


 I want to save whole XML message in sql table field which is with datatype XML(.).. how can i achieve this.


I did a prototype to do what you are asking. I actually haven’t inserted into the xml data type in SQL Server before, so I was curious if that added any complexity. I’ll just go through the mapping pieces and assume that you can easily find documentation on how to insert data into SQL Server using the Add Generated Items –> Consume Adapter Service function.

The interesting piece was writing the map. I used a simple PO schema. My sample table was XmlRepository with a ID column, RepositoryID, and the RawXml field.

The script is an inline XSLT script that is as follows:

<xsl:element name="ns3:RawXml">
<xsl:text disable-output-escaping="yes">&lt;![CDATA[</xsl:text>
<xsl:copy-of select="/" />
<xsl:text disable-output-escaping="yes">]]&gt;</xsl:text>


The question right off is why the CDATA? Well, since the value of the RawXml element that needs to be inserted into SQL Server is XML data, it has to be wrapped in a CDATA tag. Otherwise, the XML data gets validated and will not properly make it to the SQL Server. You can try it and see what I mean. Don’t worry, the CDATA gets stripped before the XML gets written to the table.

The interesting pieces are the xsl:text elements, which manually create a CDATA wrapper for the XML that ends up in the RawXml field. Apparently, the normal Grid Property for creating CDATA output doesn’t work in conjunction with the xsl:copy-of element. That tripped me up for a while!

What the map outputs is a document that looks like the following:

<ns0:Insert xmlns:array="" xmlns:ns3=""
				<![CDATA[<ns0:PurchaseOrder xmlns:ns0="http://XmlRepositoryDemo.PurchaseOrder">


The map can be called from a simple Orchestration, or even a messaging-only scenario, and sent across the wire in a request/reply as you would for normally sending data to SQL Server.

I hope this helps someone. It ended up being more than I expected!