Archive Messages with a BizTalk Pipeline to a Database

In a BizTalk project, Mikael Håkansson and me had to create a receive pipeline in BizTalk, which archives messages to the database. A colleague of mine (Christian Brückner, which blogs here sometimes, too) inspired us to use the SQL-Command UPDATETEXT to store the messages in streams to the database. The result is quite cool and can be read on Mikael's blog post BizTalk SQL Message Archive component using Forward-Only Streaming.

Hot Fix Released to use MSMQ and EdiReceive Pipeline

On April 1, 2008, Microsoft released a hot fix for the bug that we found when using the EDIReceive-Pipeline with MSMQ (see blog entry from September 2007). The hot fix can be downloaded here: http://support.microsoft.com/kb/948747/en-us.

Export/Import BizTalk Parties with Database functions

Christian has already explained how to export and import large amounts of BizTalk parties with BindingImport. Now, we were thinking of exporting the BizTalk parties directly from one BizTalkMgmtDb to a new server without BizTalk parties. You can do this in six steps:

1. Check wether there are no parties yet saved on the target server (check bts_party)
2. Temporarily disable the identity flag for the primary key columns n_id of the tables bts_party and bts_party_alias on the target database.
   
3. Copy the table bts_party from the source system to the target database (you can't copy both tables in one step due to references' integrity).
4. Enable the identity flag for column n_id in table bts_party on the target database.
5. Copy the table bts_party_alias from the source system to the target database.
6. Enable the identity flag for column n_id in table bts_party_alias on the target database.

There are also other party tables such as bts_party_sendport, bts_enlistedparty, bts_enlistedparty_operation_mapping, and bts_enlistedparty_port_mapping. However, those connect parties to sendports, role links, mappings and ports, and should be rather configured than directly changed inside the database.

If you also want to adapt more EDIFACT configurations, you have to migrate the tables that start with "EdiPartner", too!

Martin, have fun!

Sometimes, error messages don't really help. ;-)

EDI: Table passed to EDI_SEGMENTS_ADD_BLOCK is empty.

The error "EDI: Table passed to EDI_SEGMENTS_ADD_BLOCK is empty"* was thrown by the SAP .NET connector after sending an IDoc to SAP. The reason was that we've set data fields inside the EDI_DC40 element that SAP had to fill on its own, such as

• DOCNUM
• CREDAT
• CRETIM
• RCVPOR
• SNDPOR
• DOCNUM from the DATAHEADERREC element

Now, we are leaving these empty, and the IDoc can be received by SAP.

Thus, if you get the same error, check which fields you should set, and which SAP should set on its own.

----------
* in German SAP systems: "EDI: Die an EDI_SEGMENTS_ADD_BLOCK übergebene Tabelle ist leer"

Short Installation guide to integrate SAP-BizTalk connectivity in Visual Studo 2005

To integrate SAP connectivity into BizTalk 2006 R2 and Visual Studio 2005, you have to follow these steps:

1. Install the runtime version of SAP .NET Connector (NCO) for Visual Studio v1.0.x which is a prerequisite for (2) - you can download the connector from the SAP Marketplace Web site.

2. Install BizTalk Adapter v2.0 for mySAP Buesiness Suite which is available here: https://www.ms2.cn/downloads/details.aspx?FamilyId=7DBF88DE-8C23-4B25-A389-D5ACF1B1FCBE&displaylang=en

3. Install the adapter's Service Pack 1 and ignore the obsolete Error 6000-message by clicking "OK".

Using BizTalk's command xpath(msg,string) correctly

If you want to use BizTalk's xpath()-method in an Expression shape of Visual Studio's BizTalk Orchestration designer, you have to think of the return of the xpath carefully.

If you use the following:

[string] s = xpath(msg1, "/*[local-name()='Root' and namespace-uri()='http://TestBiztalkXPathCommand.Schema1'] /*[local-name()='name' and namespace-uri()='']")

to get the value at node "name", you'll receive the following error while running the orchestration:

...
Inner exception: There is an error in the XML document.
Exception type: InvalidOperationException
Source: System.Xml
[...]
Additional error information:
was not expected.
[...]
Target Site: System.Object Read_string()


The reason is that this XPath statements navigates to an XML node and, thus, an object of the class System.Xml.XmlNode is returned (and not a String). Since the XPath statement is not analyzed while building the DLL, the error occurs while running primary.

Thus, you should add /text() to your XPath statement, so that a string is returned. In this case, the following would work successfully:

[string] s = xpath(msg1, "/*[local-name()='Root' and namespace-uri()='http://TestBiztalkXPathCommand.Schema1'] /*[local-name()='name' and namespace-uri()='']/text()")


Charles Young's blogpost on xpath() was helpful to fix this problem.

Addition (June 3, 2008) : My colleague Christian found out that you should invoke some mysterious "string()"-method inside the xpath statement:

xpathExpression = "/*[local-name()='Root' and namespace-uri()='http://TestBiztalkXPathCommand.Schema1'] /*[local-name()='name' and namespace-uri()='']";
[string] s = xpath(msg1, "string(" + xpathExpression + "/text())")

Thus, you get less XPath errors (I don't know yet how that exactly works; but I know that string(...) is not an XPath expression).

Using CASE in SQL Queries

This morning, I've got to know the CASE functionality of SQL Server (which surely exists in other databases, too). With CASE you can set values for a column under specified conditions, e.g.

SELECT
Column1,
Column2 = CASE
WHEN Column1 = '1' THEN 'Y'
ELSE 'N'
END
FROM Table1

Thus, column2 will be set with 'Y' if column1 has the value '1'; otherweise 'N' will be set.

PS: An overview by Craig S Mullins helps to understand this method in more detail.

Visual Studio throws error while building: The process cannot access the file as it is being used by another process.

The error "The process cannot access the file as it is being used by another process." is thrown by Visual Studio (here 2005) if an assembly, that is used by the building process, is blocked by another process.

A useful program to find the blocking process is Process Explorer (v11.04). Furthermore, you should check other sessions on the development system and, also, other machines that could have the same solution opened remotely.