Apr 232007

Information:  This error occurs when a document is picked up by a receive location and the pipeline specified in that receive location does not recognize the data. 



1.  Check that the correct pipeline is selected in your Receive Location Properties


2.  Open Visual Studio and ensure the correct schema is used in the pipeline




3.  Right click on your schema and manually validate the file that threw the error by selecting properties -> Input Instance Filename – > Select Original File.  Then right click again on schema and select Validate Instance.  You will be manually doing what the pipeline did to throw the original error.  You should see detailed error information in the Visual Studio Output Window.

Apr 192007

Information:  This error occurs when a document is picked up by a receive location and the pipeline specified in that receive location does not recognize the data. 



1.  Check that the correct pipeline is selected in your Receive Location Properties


2.  Open Visual Studio and ensure the correct schema is used in the pipeline




3.  Right click on your schema and manually validate the file that threw the error by selecting properties -> Input Instance Filename – > Select Original File.  Then right click again on schema and select Validate Instance.  You will be manually doing what the pipeline did to throw the original error.  You should see detailed error information in the Visual Studio Output Window.

Apr 182007

In 2004 in the SDK it provided you the schema, cure but in 2006 there are documents about what is in the binding file:


and even how you can command line work with it here:


But no schema out of the box!

I needed the schema so I can take an existing excel spreadsheet of trading partners and create a binding file that will import all of them at once. I asked the product group how to extract the binding file, story and was not given the information (having access to the product group with the MVP status has its limitations obviously), viagra order but I found out through some other friends. The command to produce the schema that represents the binding file is:

xsd.exe “C:Program FilesMicrosoft BizTalk Server 2006Microsoft.BizTalk.Deployment.dll” /type:BindingInfo

Apr 162007

Failed to trigger compile-thread on the server.
(-1040) <Description for error (-1040) cannot be found. Please refer to the Trouble Shooting Guide for this description>



Restart the HIPAA service to clear this issue when using the xsd2edi.exe or compeif.exe


Don’t ask me why…

Apr 132007


Do not use this method if you are planning on doing any bulk loading. There is a 10,000 variable limitation that is reported through the BizTalk Event Log entries but is actually a SQL Server design limitation.




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 here and 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 modifications. 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 for the table name

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, for the repeating, you will change the max occurs to Unbounded

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 except 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 concatenated foreign key thru the child tables

Finishing out the relationships look like this:


Now all you 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!

Apr 122007

know I know, I should sit back and bask in the fact that I have made a very good living understanding how BizTalk works and some of the neuances in the product are overcome. But I am not satisfied. I demand perfection!

Here are a list of some of the things that I think could be improved with how Microsoft documents and delivers their content to us mere mortals.

  1. More guts of the technical architecture of what goes on behind the scenes of their products. A case in point that I am intmately familiar with, the HIPAA database. There is very little documentation on what that database has in it, and how to leverage data that is contained in there. I have written an entry on how to extract the error information that is contained in it, but that was simply found by poking around. What is the purpose of the magic column in the parame table. What are all of the other columns used for? When you validate the schema in visual studio, there should be better documentation that states that it will invoke xsd2edi and compeif. How about some examples of MSMQT, what situations would you use them, and best of all: a few examples.
  2. What would be really nice is to have a section in the online help that would list of all of the hotfixes for that product. I think that having in online would be the best place, because then it would be up to date. I would appreciate a list so I don’t spend 3 days troubleshooting an issue only to call in and the helpdesk rep state “ya, that is a known issue, we have a hotfix for that.” I don’t care if it has not been fully tested, if it saves me working like crazy only to find out that someone else has reported it and there is a hotfix. Even if there is not a hotfix and it is an open item, I would like to know that they are working on it and I will gladly call up and have my name added to the list when the hotfix becomes available I will get it.
  3. I like whitepapers, but they seem to get published 2 years after the product is out. A little too late for me… …about 2 years too late! Why can’t whitepapers be released as soon as the product is released? In order to qualify for a Beta program, you have share your experiences with the product, why can’t a whitepaper be required for Beta customers?
  4. Another irk I have is with some of BizTalk pieces. With all of the troubleshooting contained in Visual Studio, why can’t more of it be integrated for BizTalk? The pipeline testing components in the SDK, why not are they part of the VS enviornment? A test enviornment that can be invoked from within VS to test an orchestration instead of having to deploy it and ‘test’ it there? Visual Studio has the ability to create test scenarios for C# methods, it would be possible to emulate an enviorment in VS for orchestrations, right click and choose Setup, and supply sources for data and file drop locations for outbound data (the ability to read and drop in both XML and flat file structures). Also if responses are not yet available, the ability from within the Setup dialog box to auto generate responses based on the schemas.
  5. There are some best practices designs already published, convoy processing, for example. Why can’t I go online from Microsoft and download a template that gets me started, so it is part of the templates? (does it exist already?) Maybe sections in the templates section, B2B, A2A, etc.
  6. I am a picture guy, I like the pictures that used to be there in the docs online, now they are gone. Examples of the different uses of the functiods for example.
  7. I would like a better search by product feature on the msdn site. I read that it normally takes 7 clicks to find what you are looking for, and it is especially true, if not worse on the MSDN site. Can’t MS afford to buy some brainiacs away from Google to improve the hit ratio for their own site?
  8. There are a lot of great blogs from MS guys, who have more access to how things really work behind the scenes. Why can’t their blog entries be included in the documentation. Keith Lim and Kevin Lam are great examples, if I am reading about BAM, it would be great to have a link from within the documenation right to his blog, or simply having his blog become part of documentaion. If I want to understand about port definitions Kevin’s blog is a perfect place to end up. But I would NEVER know that by reading documentation.
  9. The examples in the SDK (at least in BizTalk) have very little documentation on what it is going to do, and why it is doing it. When all I can see is how to set it up.

To list a few things that I think are good, so this is not simply a rant:

  1. The SQL Server books online are pretty comprehensive. If all documentation was as thorough as SQL Server there would be less conflict in the world.
  2. Readability. The documentation is usually clear and concise.

Use this reply section to add your thoughts!

Apr 032007

A recent request I got was for information regarding retrieving error informaiton in a more readable format:

We have several clients submitting valid X12 documents but recently we started receiving X12 from a client which is not conforming to the standard. For these messages BizTalk is throwing an error into eventlog and does nothing. Unless some one monitors the eventlog there is no way we could identify that a message failed. At the same time we want to report to client in a nice way that the message they sent us is invalid. Something similar to EDIFECS or FREDI. We cannot find a simpler way to do that. Do you have any information about how we can validate X12 and reject in BizTalk in a better way?

I actually have, what I have is an orchestration that picks up the file, interrogates the sender/reciever id and then drops the file off for the HIPAA accelerator to validate. It then queries the HIPAA database and extracts the information and sends out an email. It has a delay before it runs the stored procedure that is determined by the Xref table (around 10 minutes) and then if it does not receive the XML version in the correlation set, it runs the following logic.

Here is the stored procedure that is on the HIPAA_EDIDb

CREATE PROCEDURE dbo.captureDetails @senderQual varchar(2), @senderId varchar(15), @controlNumber varchar(9) AS SELECT errtxt.descrp AS description, errors.descrp1+errors.descrp2 AS details FROM audin INNER JOIN errors ON audin.icin = errors.msgnr INNER JOIN errtxt ON errors.etc = errtxt.etc WHERE (audin.sid = @senderId) AND (audin.icr = @controlNumber) AND (audin.sidcdq =@senderQual) AND (errors.inout = '2') for xml raw--, xmldata GO

Here is the schema that is created by this stored procedure:


<?xml version="1.0"?> <xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://captureErrors" version="1.0" xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:annotation> <xs:appinfo> <msbtssql:sqlScript value="exec [captureDetails] @controlNumber=&quot; &quot;, @senderId=&quot; &quot;, @senderQual=&quot; &quot;" xmlns:msbtssql="http://schemas.microsoft.com/BizTalk/2003" /> </xs:appinfo> </xs:annotation> <xs:element name="Request"> <xs:complexType> <xs:sequence> <xs:element name="captureDetails"> <xs:complexType> <xs:attribute name="controlNumber" type="xs:string" /> <xs:attribute name="senderId" type="xs:string" /> <xs:attribute name="senderQual" type="xs:string" /> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element> <xs:element name="Response"> <xs:complexType> <xs:sequence> <xs:element minOccurs="0" maxOccurs="unbounded" name="row" xmlns:q1="http://captureErrors" type="q1:rowType" /> </xs:sequence> </xs:complexType> </xs:element> <xs:complexType name="rowType"> <xs:attribute name="description" type="xs:string" /> <xs:attribute name="details" type="xs:string" /> </xs:complexType> </xs:schema>


I have a flat file schema


<?xml version="1.0" encoding="utf-16"?> <xs:schema xmlns:ns0="http://captureErrors" xmlns="http://notificationEmail.email" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" targetNamespace="http://notificationEmail.email" xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:import schemaLocation=".\errorinfo.xsd" namespace="http://captureErrors" /> <xs:annotation> <xs:appinfo> <b:schemaInfo count_positions_by_byte="false" parser_optimization="speed" lookahead_depth="3" suppress_empty_nodes="false" generate_empty_nodes="true" allow_early_termination="false" standard="Flat File" root_reference="email" /> <schemaEditorExtension:schemaInfo namespaceAlias="b" extensionClass="Microsoft.BizTalk.FlatFileExtension.FlatFileExtension" standardName="Flat File" xmlns:schemaEditorExtension="http://schemas.microsoft.com/BizTalk/2003/SchemaEditorExtensions" /> <b:references> <b:reference targetNamespace="http://captureErrors" /> </b:references> </xs:appinfo> </xs:annotation> <xs:element name="email"> <xs:annotation> <xs:appinfo> <b:recordInfo structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" sequence_number="1" child_order="postfix" child_delimiter_type="hex" child_delimiter="0x0D 0x0A" /> <b:properties> <b:property distinguished="true" xpath="/*[local-name()='email' and namespace-uri()='http://notificationEmail.email']/*[local-name()='FileName' and namespace-uri()='']/@*[local-name()='name' and namespace-uri()='']" /> <b:property distinguished="true" xpath="/*[local-name()='email' and namespace-uri()='http://notificationEmail.email']/*[local-name()='Count' and namespace-uri()='']/@*[local-name()='number' and namespace-uri()='']" /> </b:properties> </xs:appinfo> </xs:annotation> <xs:complexType> <xs:sequence> <xs:annotation> <xs:appinfo> <b:groupInfo sequence_number="0" /> </xs:appinfo> </xs:annotation> <xs:element name="Envelope"> <xs:annotation> <xs:appinfo> <b:recordInfo structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" tag_name="Sender (ISA06:ISA05:GS02): " sequence_number="1" /> </xs:appinfo> </xs:annotation> <xs:complexType> <xs:attribute name="id" type="xs:string"> <xs:annotation> <xs:appinfo> <b:fieldInfo sequence_number="1" justification="left" /> </xs:appinfo> </xs:annotation> </xs:attribute> </xs:complexType> </xs:element> <xs:element name="ControlNumber"> <xs:annotation> <xs:appinfo> <b:recordInfo structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" sequence_number="2" child_order="infix" child_delimiter_type="char" child_delimiter="," tag_name="Control Number: " /> </xs:appinfo> </xs:annotation> <xs:complexType> <xs:attribute name="ControlNo" type="xs:string"> <xs:annotation> <xs:appinfo> <b:fieldInfo sequence_number="1" justification="left" /> </xs:appinfo> </xs:annotation> </xs:attribute> </xs:complexType> </xs:element> <xs:element name="Count"> <xs:annotation> <xs:appinfo> <b:recordInfo sequence_number="3" structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" tag_name="Count: " /> </xs:appinfo> </xs:annotation> <xs:complexType> <xs:attribute name="number" type="xs:int"> <xs:annotation> <xs:appinfo> <b:fieldInfo sequence_number="1" justification="left" /> </xs:appinfo> </xs:annotation> </xs:attribute> </xs:complexType> </xs:element> <xs:element name="FileName"> <xs:annotation> <xs:appinfo> <b:recordInfo sequence_number="4" structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" tag_name="Filename: " /> </xs:appinfo> </xs:annotation> <xs:complexType> <xs:attribute name="name" type="xs:string"> <xs:annotation> <xs:appinfo> <b:fieldInfo sequence_number="1" justification="left" /> </xs:appinfo> </xs:annotation> </xs:attribute> </xs:complexType> </xs:element> <xs:element maxOccurs="1" name="Data"> <xs:annotation> <xs:appinfo> <b:recordInfo sequence_number="5" structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" child_order="prefix" child_delimiter_type="hex" child_delimiter="0x0D 0x0A 0x0D 0x0A" /> </xs:appinfo> </xs:annotation> <xs:complexType> <xs:sequence> <xs:annotation> <xs:appinfo> <b:groupInfo sequence_number="0" /> </xs:appinfo> </xs:annotation> <xs:element maxOccurs="unbounded" name="Details"> <xs:annotation> <xs:appinfo> <b:recordInfo structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" tag_name="Error #" child_order="infix" child_delimiter_type="char" child_delimiter="= " sequence_number="1" /> </xs:appinfo> </xs:annotation> <xs:complexType> <xs:sequence> <xs:annotation> <xs:appinfo> <b:groupInfo sequence_number="0" /> </xs:appinfo> </xs:annotation> <xs:element maxOccurs="1" name="Number" type="xs:string"> <xs:annotation> <xs:appinfo> <b:fieldInfo sequence_number="1" justification="left" /> </xs:appinfo> </xs:annotation> </xs:element> <xs:element maxOccurs="1" name="Information"> <xs:annotation> <xs:appinfo> <b:recordInfo sequence_number="2" structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" child_order="infix" child_delimiter_type="char" child_delimiter=" " /> </xs:appinfo> </xs:annotation> <xs:complexType> <xs:sequence> <xs:annotation> <xs:appinfo> <b:groupInfo sequence_number="0" /> </xs:appinfo> </xs:annotation> <xs:element maxOccurs="1" name="Info" type="xs:string"> <xs:annotation> <xs:appinfo> <b:fieldInfo sequence_number="1" justification="left" /> </xs:appinfo> </xs:annotation> </xs:element> <xs:element maxOccurs="1" name="Detail" type="xs:string"> <xs:annotation> <xs:appinfo> <b:fieldInfo sequence_number="2" justification="left" /> </xs:appinfo> </xs:annotation> </xs:element> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element> </xs:schema>


The orchestration runs and calls the stored procedure, maps the data, determines who gets it (based on a role link) and send out the email.

The email’s contents look like this:

Sender (ISA06:ISA05:GS02): 1234:ZZ:1234

Control Number: 166

Count: 0

Filename: c:\import\8371234564789.txt Error #1= The check condition has not been met. Contact the sender.; source format: [5 00401 ,X12-4010]

source document: [837 004010DEFAULT X X098A1,Health Care Claim: Professional]

source segment: [data#9945,def#22,tag=NM1 ,name=TS837Q1_2010BA_NM1_SubscriberName_A1>DEFAULT]

source element: [def#9,elm#9,comp#0,name=TS837Q1_2010BA_NM109__SubscriberPrimaryIdentifier_A1>DEFAULT],

value: []


(((TS837Q1_2010BA_NM108__IdentificationCodeQualifier_A1>DEFAULT == [] AND TS837Q1_2010BA_NM109__SubscriberPrimaryIdentifier_A1>DEFAULT == [] ) OR

You have to be using the HIPAA receive location, and not the pipeline component, for reason I have blogged about earlier.