BizTalk 2006 DB2 BizTalk Adapters: Send adapters

I have just finished changing an implementation that used the BizTalk 2006 DB2 receive adapters to one that uses BizTalk 2006 DB2 send adapters to call the same stored procedures and want to describe all the problems I encountered. Please comment if you know a better way of completing the journey I describe below.

I had been calling a stored procedure using a DB2 receive adapter with the following XML response schema.

<?xml version=”1.0″ encoding=”utf-16″?>
<xs:schema xmlns:b=”http://schemas.microsoft.com/BizTalk/2003″ elementFormDefault=”qualified” targetNamespace=”http://BT_GET_SKUS” xmlns:xs=”http://www.w3.org/2001/XMLSchema”>
<xs:element name=”SKUUpdates”>
    <xs:complexType>
      <xs:sequence>
        <xs:element xmlns:q1=”http://BT_GET_SKUS” minOccurs=”0″ maxOccurs=”unbounded” name=”Table1″ type=”q1:Table1Type” />
      </xs:sequence>
    </xs:complexType>
</xs:element>
<xs:complexType name=”Table1Type”>
    <xs:attribute name=”SKUID” type=”xs:string” use=”required” />
    <xs:attribute name=”DESC” type=”xs:string” />
    <xs:attribute name=”SHTDESC” type=”xs:string” use=”required” />
    <xs:attribute name=”UNTWGT” type=”xs:decimal” use=”required” />
    <xs:attribute name=”CPQ” type=”xs:decimal” use=”required” />
</xs:complexType>
</xs:schema>

I changed this to be a send adapter using the DB2 adapter wizard and now my request response schema looked like;

skurequest

Horror! My response schema has disappeared into an any element called ResultSets. Now I have to do more work before the messages will work with my existing implementation. I’ll talk about how I solved this one later one but the first step was to construct a message to send as the request to the send adapter. The simplest way to do this is to generate and instance from the schema and use this in a Message Assignment shape like;

sKUUpdateRequestXDoc = new System.Xml.XmlDocument();

sKUUpdateRequestXDoc.LoadXml(@”<ns0:SKUSRequest xmlns:ns0=http://BT_GET_SKUS_AS_RESULTSET>
<sync>
    <StoredProcedure>
      <BT_GET_SKUS ROWCNT=”“/>
    </StoredProcedure>
</sync>
</ns0:SKUSRequest>”);

sKUUpdateRequest.parameters = sKUUpdateRequestXDoc ;

One gotcha here is that if you use the qualified version of the message you get an error like “The adapter failed to transmit message going to send port “SKU” with URL “DB2://mac:446/DEV/BT_GET_WORKASSIGNMENTS”. It will be retransmitted after the retry interval specified for this Send Port. Details:” in SQLSTATE: 42710, SQLCODE: -601″. If you use an unqualified version the call to the DB2 procedure and returns a response message that looks like

<?xml version=”1.0″ encoding=”utf-16″ ?>

<BT_GET_SKUS ROWCNT=”“ />

</ResultSets>

</Success>

</SKUSResponse>

The second step is to transform this message into the original http://getSKUS message in order to re-use the existing processing in the orchestration. Things are not that simple because the ResultSet node is an any element . I found that the easiest thing was to use a map do a mass copy of the Result node to my original schema.

skumap

The transformed message looks like;

<ns0:SKUUpdates xmlns:ns0=”http://BT_GET_SKUS” xmlns:ns1=”http://BT_GET_SKUS_AS_RESULTSET“>

</ns0:SKUUpdates>

This is not a valid instance of our original schema because it contain extra namespace qualifiers. The third step was to change the original http://getSKUS schema so that it contains the extra namespace qualifiers. I created a new schema for the Table1 node like so;

<?xml version=”1.0″ encoding=”utf-16″?>
<xs:schema xmlns:tns=”http://BT_GET_SKUS_AS_RESULTSET” xmlns:b=”http://schemas.microsoft.com/BizTalk/2003” attributeFormDefault=”unqualified” elementFormDefault=”qualified” targetNamespace=”http://BT_GET_SKUS_AS_RESULTSET” xmlns:xs=”http://www.w3.org/2001/XMLSchema“>
<xs:element name=”Table1″>
    <xs:complexType>
      <xs:attribute name=”SKUID” type=”xs:string” use=”required” />
      <xs:attribute name=”DESC” type=”xs:string” />
      <xs:attribute name=”SHTDESC” type=”xs:string” use=”required” />
      <xs:attribute name=”UNTWGT” type=”xs:decimal” use=”required” />
      <xs:attribute name=”CPQ” type=”xs:decimal” use=”required” />
    </xs:complexType>
</xs:element>
</xs:schema>

This schema was imported into the original http://getSKUS schema and then the Table1 node was deleted and recreated by reference like this;

<?xml version=”1.0″ encoding=”utf-16″?>
<xs:schema xmlns:ns0=”http://BT_GET_SKUS” xmlns:b=”http://schemas.microsoft.com/BizTalk/2003” elementFormDefault=”qualified” targetNamespace=”http://BT_GET_SKUS” xmlns:xs=”http://www.w3.org/2001/XMLSchema“>
<xs:import schemaLocation=”.\skusresultset.xsd” namespace=”http://BT_GET_SKUS_AS_RESULTSET” />
<xs:annotation>
    <xs:appinfo>
      <b:references>
        <b:reference targetNamespace=”http://BT_GET_SKUS_AS_RESULTSET” />
      </b:references>
    </xs:appinfo>
</xs:annotation>
<xs:element name=”SKUUpdates”>
    <xs:complexType>
      <xs:sequence>
        <xs:element xmlns:q1=”http://BT_GET_SKUS_AS_RESULTSET” minOccurs=”0″ maxOccurs=”unbounded” ref=”q1:Table1″ />
      </xs:sequence>
    </xs:complexType>
</xs:element>
</xs:schema>

Finally I have my original message and can re-use the previously existing process.