When stepping up the 4010 834 Document to 5010 834, one requirement is for the outgoing message to have a “^” for the ISA 11 segment. Normally we would select the “Repetition Separator” radio button in the Agreement Properties for this to be added in the outgoing envelope.
In the 4010 834 the ISA 11 uses a “U” for the repetition separator. However, when we tested and the message was sent through, we received invalid character errors. I checked all the segments that were containing errors, and I did not find any invalid characters.
In the EDI document I was using, all the segments were in caps. After playing around with the data, I noticed I did not receive errors when the data was in lowercase. On a hunch, I then set all the lowercase u’s to caps, and the errors went away.
As the final step, I created a simple send pipeline component that sets the repetition separator from a “U” to a “^”, and set the envelope setting to use “Standard Identifier”. With this pipeline in place, the message processed without errors.
Recently a client submitted the requirement of matching an incoming SSN to a Member ID on a 834 EDI document. They also requested we create a text file to report any members that did not have a match. Our solution was to create an orchestration to handle all of the requirements.
The first task was to create a map for the source 834 to the destination schema. Since this is not the focus of this blog post, I will not go into any details of the mapping, but focus on the requirement in question.
In the map we needed to do the matching of the SSN to the Member ID. This was handled by using an external assembly that calls a stored procedure. The stored procedure does the entire member matching and inserts pertinent data on the member that does not have a member id match. In our scenario, if a match exists, we return the member ID. If a match does not exist, we return ‘no match’, and insert the member’s SSN and Birthdate into a table that contains non-matches.
For the . txt file output we also used an external assembly to call a stored procedure to create our file. This stored procedure accepts 1 input of our filename and uses the “bcp” utility http://msdn.microsoft.com/en-us/library/ms162802.aspx to copy our table information into a .txt file.
Our orchestration ended up looking something like this:
One common task in a BizTalk map is performing some type of database lookup. While there are several database functoids available for this, quite often the easiest (and most appropriate) way is to create an external assembly to do the lookup. An external assembly allows for the full power of the .NET framework and the flexibility to test and develop outside of the BizTalk platform.
An example of an external assembly for lookup is shown below. This method accepts two input parameters:
- The first is the value to pass to the stored procedure
- The second is the connection string to the database. The connection string is added as an input so that the value can be passed to the map for testing. In production, a blank value can be passed and the connection string will be set through the BizTalk configuration file.
The method creates the necessary SQL objects to create a sqlcommand, add parameters, connect to the database and retrieve a scalar result.
Once the code is complete and tested, sign the assembly, build it and add a reference to it in the mapping project. The code can then be called from a map using a custom Scripting functoid. Select External Assembly, the correct class name, and the appropriate method – as shown below:
The input parameters can be added as shown here (for the connection string either add the data source or add a blank input so that the setting in the BizTalk configuration file can be used).
When EDI documents arrive in BizTalk, they are validated against the party settings and the schema associated with the document type received. If the document cannot be validated, the specific error will be caught in BizTalk, and made available through the suspended messages reporting area and in the Windows Event log. An example of an error is shown in the following screen shot – this states that the error is in position 32. This position corresponds to the actual XML segment location in the corresponding XML document (all EDI documents are handled as XML within BizTalk.
Here is the corresponding XML:
The first step in onboarding a new trading partner that will be delivering inbound data to your organization is the review and validation of their inbound data. The moment you identify a trading partner that will be delivering data to you, request some sample data from them. Through analysis of this data, you will be able to determine what resourcing, timeline, and development will likely be needed in order to onboard the partner.
A trading partner may be delivering data in true EDI format (such as an 837), or in a proprietary format (CSV, XML, ECSIF, etc.), and it is critical to review this data as early in the onboarding process as possible. You will need to identify what level of work will be required to map this inbound data to a format that will work within your system. Even in cases where the trading partner will be delivering data in (for example) 837 5010 format, and your processes already accept this same format from other trading partners, there will still likely be inconsistencies in the data that will prevent you from simply accepting the data and processing it. Different organizations model their data differently, populate their EDI documents differently, and require different fields and structures. What your organization may need will likely differ from what the trading partner will – by default – be delivering.
Many organizations try to come up with a “standard timeline” for onboarding new partners – but what you will find is that every integration is unique, and while some trading partners may be able to be onboarded in a very short time, others will require a tremendous amount of development and testing in order to be ready for a production environment. The sooner you can review their data, the sooner you will be able to give an accurate estimate for the effort required to onboard them.