Copying functoids from one map to another

Have you ever wanted to copy a complex set of logic from one map to another? One of my young colleagues, Stephen Scott showed me a quick way to do this the other day. Here it is.

I had this map with many pages.

 

 

 

 

 

 

 

 

 

 

 

I wanted to change it to a map with two inputs instead of one.

 

 

 

 

 

 

 

 

 

 

The trick is as follows;

1. Open the first map in a text editor. You will see something like    

GenerateDefaultFixedNodes=”Yes” PreserveSequenceOrder=”No” CopyPIs=”No” method=”xml” xmlVersion=”1.0″ IgnoreNamespacesForLinks=”Yes”>
  <SrcTree>
    <Reference Location=”SubmitContactDetailsRequest” />
  </SrcTree>
  <TrgTree>
    <Reference Location=”InternalMaintainQuoteRequest” />
  </TrgTree>
  <ScriptTypePrecedence>
    <CSharp Enabled=”Yes” />
    <ExternalAssembly Enabled=”Yes” />
    <VbNet Enabled=”Yes” />
    <JScript Enabled=”Yes” />
    <XsltCallTemplate Enabled=”Yes” />
    <Xslt Enabled=”Yes” />
  </ScriptTypePrecedence>
  <TreeValues>
    <TestValues />
    <ConstantValues />
  </TreeValues>
  <Pages>
    <Page Name=”Common”>
      <Links>
        <Link LinkID=”1″ LinkFrom=”/*[local-name()='&lt;Schema&gt;']/*[local-name()='SubmitContact']/*[local-name()='QuoteDetails']/*[local-name()='QuoteID']” LinkTo=”/*[local-name()='&lt;Schema&gt;']/*[local-name()='MaintainQuoteRequest']/*[local-name()='QuoteDetails']/*[local-name()='SourceQuoteID']” Label=”" />
        <Link LinkID=”2″ LinkFrom=”/*[local-name()='&lt;Schema&gt;']/*[local-name()='SubmitContact']/*[local-name()='QuoteDetails']/*[local-name()='Source']” LinkTo=”/*[local-name()='&lt;Schema&gt;']/*[local-name()='MaintainQuoteRequest']/*[local-name()='QuoteDetails']/*[local-name()='SourceSystem']” Label=”" />
        <Link LinkID=”3″ LinkFrom=”/*[local-name()='&lt;Schema&gt;']/*[local-name()='SubmitContact']/*[local-name()='QuoteDetails']/*[local-name()='ProductType']” LinkTo=”/*[local-name()='&lt;Schema&gt;']/*[local-name()='MaintainQuoteRequest']/*[local-name()='QuoteDetails']/*[local-name()='Product']” Label=”" />
        <Link LinkID=”4″ LinkFrom=”/*[local-name()='&lt;Schema&gt;']/*[local-name()='SubmitContact']/*[local-name()='QuoteDetails']/*[local-name()='Progress']” LinkTo=”/*[local-name()='&lt;Schema&gt;']/*[local-name()='MaintainQuoteRequest']/*[local-name()='QuoteDetails']/*[local-name()='QuoteProgress']” Label=”" />
        <Link LinkID=”5″ LinkFrom=”/*[local-name()='&lt;Schema&gt;']/*[local-name()='SubmitContact']/*[local-name()='QuoteDetails']/*[local-name()='Status']” LinkTo=”/*[local-name()='&lt;Schema&gt;']/*[local-name()='MaintainQuoteRequest']/*[local-name()='QuoteDetails']/*[local-name()='QuoteStatus']” Label=”" />
        <Link LinkID=”6″ LinkFrom=”/*[local-name()='&lt;Schema&gt;']/*[local-name()='SubmitContact']/*[local-name()='QuoteDetails']/*[local-name()='PolicyStart']” LinkTo=”/*[local-name()='&lt;Schema&gt;']/*[local-name()='MaintainQuoteRequest']/*[local-name()='QuoteDetails']/*[local-name()='StartDate']” Label=”" />
        <Link LinkID=”7″ LinkFrom=”/*[local-name()='&lt;Schema&gt;']/*[local-name()='SubmitContact']/*[local-name()='QuoteDetails']/*[local-name()='PolicyEnd']” LinkTo=”/*[local-name()='&lt;Schema&gt;']/*[local-name()='MaintainQuoteRequest']/*[local-name()='QuoteDetails']/*[local-name()='EndDate']” Label=”" />
        <Link LinkID=”8″ LinkFrom=”/*[local-name()='&lt;Schema&gt;']/*[local-name()='SubmitContact']/*[local-name()='QuoteDetails']/*[local-name()='OriginatingURL']” LinkTo=”/*[local-name()='&lt;Schema&gt;']/*[local-name()='MaintainQuoteRequest']/*[local-name()='QuoteDetails']/*[local-name()='OriginatingURL']” Label=”" />
        <Link LinkID=”9″ LinkFrom=”/*[local-name()='&lt;Schema&gt;']/*[local-name()='SubmitContact']/*[local-name()='QuoteDetails']/*[local-name()='Link']” LinkTo=”/*[local-name()='&lt;Schema&gt;']/*[local-name()='MaintainQuoteRequest']/*[local-name()='QuoteDetails']/*[local-name()='QuoteLink']” Label=”" />
        <Link LinkID=”10″ LinkFrom=”/*[local-name()='&lt;Schema&gt;']/*[local-name()='SubmitContact']/*[local-name()='QuoteDetails']/*[local-name()='Channel']” LinkTo=”/*[local-name()='&lt;Schema&gt;']/*[local-name()='MaintainQuoteRequest']/*[local-name()='QuoteDetails']/*[local-name()='SalesChannel']” Label=”" />
        <Link LinkID=”11″ LinkFrom=”/*[local-name()='&lt;Schema&gt;']/*[local-name()='SubmitContact']/*[local-name()='QuoteDetails']/*[local-name()='LastModifiedDate']” LinkTo=”/*[local-name()='&lt;Schema&gt;']/*[local-name()='MaintainQuoteRequest']/*[local-name()='QuoteDetails']/*[local-name()='LastModifiedDate']” Label=”" />
        <Link LinkID=”12″ LinkFrom=”/*[local-name()='&lt;Schema&gt;']/*[local-name()='SubmitContact']/*[local-name()='UserDetails']/*[local-name()='UserName']” LinkTo=”/*[local-name()='&lt;Schema&gt;']/*[local-name()='MaintainQuoteRequest']/*[local-name()='QuoteDetails']/*[local-name()='AccountName']” Label=”" />
        <Link LinkID=”13″ LinkFrom=”1″ LinkTo=”/*[local-name()='&lt;Schema&gt;']/*[local-name()='MaintainQuoteRequest']/*[local-name()='QuoteDetails']/*[local-name()='SourceID']” Label=”" />
      </Links>
      <Functoids>
        <Functoid FunctoidID=”1″ X-Cell=”56″ Y-Cell=”213″ Functoid-FID=”107″ Functoid-Name=”String Concatenate” Label=”ToBAssigned”>
          <Input-Parameters>
            <Parameter Type=”Constant” Value=”ToBAssigned” Guid=”{AF7EB9D5-B802-4615-A366-DAF4CD18B182}” />
          </Input-Parameters>
        </Functoid>
      </Functoids>
    </Page>
    <Page Name=”ContactMeRequested”>
      <Links>

………..more

2. Copy every thing between the Pages tag to the clipboard.
3. Open the second map in a text editor. You will something like

<?xml version=”1.0″ encoding=”utf-16″?>
<!– Generated using BizTalk Mapper on Sun, Oct 11 2009 07:54:38 AM –>
<mapsource Name=”BizTalk Map” BizTalkServerMapperTool_Version=”2.0″ Version=”2″ XRange=”100″ YRange=”420″ OmitXmlDeclaration=”Yes” TreatElementsAsRecords=”No” OptimizeValueMapping=”No” GenerateDefaultFixedNodes=”Yes” PreserveSequenceOrder=”No” CopyPIs=”No” method=”xml” xmlVersion=”1.0″ IgnoreNamespacesForLinks=”Yes”>
  <SrcTree>
    <xs:schema xmlns:tns=”http://schemas.microsoft.com/BizTalk/2003/aggschema” xmlns:b=”http://schemas.microsoft.com/BizTalk/2003″ xmlns:ns1=http://SubmitContactDetails xmlns:ns2=http://SubmitContactDetailsResponse targetNamespace=”http://schemas.microsoft.com/BizTalk/2003/aggschema” xmlns:xs=”http://www.w3.org/2001/XMLSchema”>
      <xs:import schemaLocation=”SubmitContactDetailsRequest” namespace=http://SubmitContactDetails />
      <xs:import schemaLocation=”SubmitContactDetailsResponse” namespace=http://SubmitContactDetailsResponse />
      <xs:element name=”Root”>
        <xs:complexType>
          <xs:sequence>
            <xs:element name=”InputMessagePart_0″>
              <xs:complexType>
                <xs:sequence>
                  <xs:element ref=”ns1:SubmitContact” />
                </xs:sequence>
              </xs:complexType>
            </xs:element>
            <xs:element name=”InputMessagePart_1″>
              <xs:complexType>
                <xs:sequence>
                  <xs:element ref=”ns2:SubmitContactResponse” />
                </xs:sequence>
              </xs:complexType>
            </xs:element>
          </xs:sequence>
        </xs:complexType>
      </xs:element>
    </xs:schema>
  </SrcTree>
  <TrgTree>
    <Reference Location=”Tower.CRM.InternalMaintainQuoteRequest” />
  </TrgTree>
  <ScriptTypePrecedence>
    <CSharp Enabled=”Yes” />
    <ExternalAssembly Enabled=”Yes” />
    <VbNet Enabled=”Yes” />
    <JScript Enabled=”Yes” />
    <XsltCallTemplate Enabled=”Yes” />
    <Xslt Enabled=”Yes” />
  </ScriptTypePrecedence>
  <TreeValues>
    <TestValues />
    <ConstantValues />
  </TreeValues>
  <Pages>
    <Page Name=”Page 1″>
      <Links />
      <Functoids />
    </Page>
  </Pages>
</mapsource>

4. Now paste the contents of the clipboard between the two pages tags.
5. Do a search and replace of [local-name()='SubmitContact'] with [local-name()='Root']/*[local-name()='InputMessagePart_0']/*[local-name()='SubmitContact']
6. Save the file.
7. Now open the change in your Biztalk project and you will see your all the functoids and other artifactd have  been copied to the new map.

Catching a WCF fault in a BizTalk orchestration

I wanted to catch a WCF fault. A quick search gave many hits about catching un-typed WCF faults for example http://geekswithblogs.net/paulp/archive/2008/06/23/123096.aspx and http://blogs.msdn.com/paolos/archive/2009/05/22/how-to-throw-typed-fault-exceptions-from-orchestrations-published-as-wcf-services.aspx. There is also an SDK example referenced in the blog by Paul Petrov. This blog describes how to catch a standard fault instead of an un-typed fault because I did not find any examples of this simpler case. I have recorded this here because it was not obvious to me from the examples above how to do it.

The web service that i was working with did not return a custom fault just a standard fault. I decided to follow the same general pattern given in the articles above but  instead of creating my own custom exception schema I referenced   http://schemas.xmlsoap.org/soap/envelope/ from the BTS system schemas (BTS.soap_envelope_1__1.Fault). Thus there is one less step than the Petrov’s pattern i.e.

1. Create a  new multi-part message type CalculatePremium_WCFFault that has part of type BTS.soap_envelope_1__1.Fault.
2. Add to my CalculatePremiumPortType operation a new GenericWCFFault fault of type BTS.soap_envelope_1__1.Fault.
3. Add an exception handler CatchGenericWCFFault for this new fault

Now I tried following the SDK example and modified  the Inbound BizTalk message body area on the send port to my web service, doing the following:
        a.    Select Path–content located by body path option.
        b.    In the Body path expression field, enter /*[local-name()='Fault']/*[local-name()='Detail']/* | /*[local-name()='PremiumResponse'].
        c.    From the Node encoding drop-down list, select Xml.
I also made sure that the Propagate fault message option is selected in the Error handling area. This did not work and I got an error probably because I did not get the body path expression correct.

 To my surprise when I switched back and selected the default Body  under body path option the WCF fault was caught and processed correctly. Now all is clear. I don’t have to use an XPath expression on the send port configuration because the general SOAP fault schema is known to the receive pipeline. The fault was copied in the Copy Errors and Exceptions shape using the following code

//Assign variables for WCF Fault where wcfFault is the exception of type PremiumCalcWebServiceLink[MyNameSpace.CalculatePremiumPortType].Operation_1.GenericWCFFault

wcfFaultStringXDoc = wcfFault.faultString;
sErrorMsg = wcfFaultStringXDoc.OuterXml;

In summary I can now catch all unhandled exceptions from my external web service.

“Failed to update MessageBox server while creating BizTalk Host”

 I was trying to add a new host to a new BizTalk server and got this error first;

“failed to create SQL login and grant SQL privilege configurations on the Management database server “SQL07DEV” / database name “BizTalkMgmtDb” while creating BizTalkHost “test”

with the following additional information:

“This operation requires granting or revoking of SQL database level user access. Ensure that you have sufficient SQL permission to perform this operation. You must either be: 1) a member of the ‘sysadmin’ SQL fixed server role; or a member of the ‘db_owner’ or db_ddladmin’ SQL fixed database role. Contact your SQL database administrators for further assistance”

So I contacted our DBA. I was in the BizTalk Administrators group and in the SSOAdministrators group on that SQL server. He granted the BizTalkAdminstrators group db_ddladmin on the database above.

I tried to create a host and got a different error. This time I needed db_accessadmin to the same database. This role was granted and then I got the similar error but on the BizTalkDTAdb database.

After granting these roles on all the BizTalk database I got the error in the title namely;

“Failed to update MessageBox server will creating BizTalk Host. Couldn’t create SQL login or update SQL roles for BizTalk Host Windows group “HOME\BizTalk Host User Group” to access the MessageBox server “SQL07DEV\BizTalkMsgBoxDb”. The role BTS_test_USERS’ does not exist in the current database. Cannot find the user ‘BTS_test_USERS’, because it does not exist or you do not have permission….”

What now? This time there are no instructions on what to do. After looking up http://msdn.microsoft.com/en-us/library/aa559845(BTS.10).aspx I discovered we had done all the right things namely;

User rights granted to BizTalk Server Administrators

securityadmin SQL Server role on all SQL Servers

db_securityadmin and db_accessadmin SQL Server Database roles in the BizTalk Tracking, Rule Engine, BizTalk Management, BAM Primary Import and BizTalk MessageBox databases

db_ddladmin SQL Server Database role on all BizTalk MessageBox databases

SSO Affiliate administrators

I have reached this stage many times with other DBA’s and at this stage they have tossed in the towel and granted my login ‘sysadmin’ rights while I created the BizTalk hosts. This DBA was different he started to dig deeper.

He discovered that i could not create a new role because the role cannot be owned by the group that was in. He solved the problem by adding my login a  user of the SQL server. Now when i try to create a host there a no errors and the host is created. If you now look at the roles you find that they are owned by my login. The owner was then changed to a more suitable SQL user.

How to copy the entire node to element of string type in a map

 

I wanted to copy the entire Contact node in the map below to the ContactDetails element of type string.

I could have done this in an orchestration by assigning the node to an XmlDocument variable and then assigning the XmlDocument.InnerText to a distinguished field in the outbound message but I wanted to do this in map  because I plan to put the map on the outbound port and not in an orchestration.

My  first try almost worked. I used a scripting functoid with this Inline XSLT.

<!–MassCopy alternative that does not copy the namespace.–>

<ns1:ContactDetails

<xsl:copy-of select=”*[local-name()='Contact']“/>

</ns1:ContactDetails>

This almost works but I get this error on validation;

The element ‘http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo:ContactDetails’ cannot contain child element ‘http://Blah.ContactDetails:Contact’ because the parent element’s content model is text only.

The problem here is that it’s copying the node structure to the outbound message and not mapping the node structure to a string.

So i have tried two solutions from posts by Greg Forsyth namely http://www.biztalkgurus.com/forums/t/12350.aspx and http://connectedpawns.wordpress.com/2008/10/08/call-a-net-assembly-from-custom-xslt/ ( I had to use my blog because the original link seems to be broken). I have decided to record how I did it because Greg’s instructions are very brief and it took me a while to work out what to do.

The first step is to create a helper class and deploy it to the GAC .

using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using System.Xml.XPath;

namespace CopyXML2TextNodeBTSDemo.Utility
{
    public class Utilities
    {
        public static string ConvertNodeToXmlString(XPathNodeIterator node)

        { return node.Current.OuterXml; }

    }
}

Secondly reference this assembly in the BizTalk map project.

Add a scripting functoid to the map , choose external assembly as the scripting type with the values shown below and connect it to the a field in the outbound schema.

Validate the map to generate a xsl file and a Custom extension XML file. The output is shown below

 

Invoking component.….CopyXML2TextNodeBTSDemo.Maps\SubmitContactDetails2XMLdatafieldasTEXT.btm: The compilation is using the CustomXslt and CustomExtensionXml tags to generate the output.  The map content is ignored.
….CopyXML2TextNodeBTSDemo.Maps\SubmitContactDetails2XMLdatafieldasTEXT.btm: The output XSLT is stored in the following file: <file:///C:\..\_MapData\SubmitContactDetails2XMLdatafieldasTEXT.xsl> 
  ….\SubmitContactDetails2XMLdatafieldasTEXT.btm: The Extension Object XML is stored in the following file: <file:///C:\..\_MapData\SubmitContactDetails2XMLdatafieldasTEXT_extxml.xml>
Component invocation succeeded.

Save both files and add them to the BizTalk map project.

Change the map to use a the generated xsl file as the custom XSLT and the Extension Object XML file as shown below.

Edit xsl file like so;

<?xml version=”1.0″ encoding=”UTF-16″?>
<xsl:stylesheet xmlns:xsl=”http://www.w3.org/1999/XSL/Transform” xmlns:msxsl=”urn:schemas-microsoft-com:xslt” xmlns:var=”http://schemas.microsoft.com/BizTalk/2003/var” exclude-result-prefixes=”msxsl var s0 ScriptNS0″ version=”1.0″ xmlns:ns0=”http://CopyXML2TextNodeBTSDemo.XMLdatafieldasTEXT” xmlns:s0=”http://CopyXML2TextNodeBTSDemo.SubmitContactDetails” xmlns:ScriptNS0=”http://schemas.microsoft.com/BizTalk/2003/ScriptNS0″>
    <xsl:output omit-xml-declaration=”yes” method=”xml” version=”1.0″ />
    <xsl:template match=”/”>
        <xsl:apply-templates select=”/s0:SubmitContactDetails” />
    </xsl:template>
    <xsl:template match=”/s0:SubmitContactDetails”>
        <ns0:ContainsXMLfieldasText>
            <ContactId>
                <xsl:value-of select=”ContactId/text()” />
            </ContactId>
            <xsl:apply-templates  select=”Contact”/>
        </ns0:ContainsXMLfieldasText>
    </xsl:template>
    <xsl:template  match=”Contact”>
        <xsl:variable name=”var:v1″ select=”ScriptNS0:ConvertNodeToXmlString(.)” />
        <ContactDetails>
            <xsl:value-of select=”$var:v1″ />
        </ContactDetails>
    </xsl:template>
</xsl:stylesheet>

Now if you test the map you get the desired output. Whew!!

Using LoadGen to test Wcf-NetTCP binding

 

I have just returned from holiday and have had to do a lot of performance monitoring on WCF adapters. It took me a while to work out the settings in the LoadGenConsole.exe to test a WCF-NetTCP binding so i thought I would reproduce it here. ( See http://connectedthoughts.wordpress.com/2007/11/05/loadgen-2007-and-a-simple-biztalk-wcf-test/ for good guide on how to use LoadGen to  test WCF adapters ).

<?xml version="1.0" ?>
<configuration>
  <system.serviceModel>

    <client>

      <endpoint name="WcfgetPremiumEndPoint"

                address="http://localhost/BizTalkWcfgetPremiumService/WcfgetPremiumService.svc"

                binding="basicHttpBinding"

                bindingConfiguration="WcfgetPremiumEndBinding"

                contract="WcfTransport.IChannelAsyncNonTransactional"/>
      <endpoint name="WcfnetTCPgetPremiumEndPoint"

          address="net.tcp://localhost/WCFgetPremium.NetTCP"

          binding="netTcpBinding"

          bindingConfiguration="WcfnetTCPgetPremiumEndBinding"

          contract="WcfTransport.IChannelAsyncNonTransactional"/>

    </client>

    <bindings>

      <basicHttpBinding>

        <binding name="WcfgetPremiumEndBinding"

                openTimeout="23:59:59" receiveTimeout="23:59:59" closeTimeout="23:59:59" sendTimeout="23:59:59">

          <security mode="None">

          </security>

        </binding>

      </basicHttpBinding>

      <netTcpBinding>

        <binding name="WcfnetTCPgetPremiumEndBinding"

                openTimeout="23:59:59" receiveTimeout="23:59:59" closeTimeout="23:59:59" sendTimeout="23:59:59">

          <security mode="Transport">

          </security>

        </binding>

      </netTcpBinding>

    </bindings>

  </system.serviceModel>
    <system.net>
        <defaultProxy>
            <proxy autoDetect="false"/>
        </defaultProxy>

        <connectionManagement>
            <add address = "*" maxconnection = "400" />
        </connectionManagement>
    </system.net>

    <!–<system.diagnostics>
        <trace autoflush="true" indentsize="4">
            <listeners>
                <add name="LogConsoleListener" type="System.Diagnostics.ConsoleTraceListener"/>
            </listeners>
        </trace>
    </system.diagnostics>–>

</configuration>

Performance of the WCF-Basic HTTP Adapter

Zeeshan recently wrote about performance problems using the SOAP adapter( http://zeetalks.wordpress.com/2009/04/29/biztalk-performance-problems-using-the-soap-adapter/ ). In that article one of the solutions he suggested to the problem was to use one of the WCF adapters. Here I describe performance measure measurements using the WCF-Basic HTTP adapter.

I used Thiago’s Load Gen 2007 test to measure request-response message latency that he has described previously ( http://connectedthoughts.wordpress.com/2007/11/05/loadgen-2007-and-a-simple-biztalk-wcf-test/). It is easy to measure the request-response latency if you use a dedicated isolated host for your receive adapter.

The first orchestration test contained a WCF-Basic HTTP receive adapter exposed by BizTalk as shown below. The average message latency was about 0.8-1.0 seconds.

The second orchestration test contained the same WCF-Basic HTTP receive adapter exposed by BizTalk  but also sent the message on after some mapping to an external WCF web service as shown below. The average message latency was now about 10-11 seconds.

In order to identify where the bottleneck was the external web service was replace by a simple web service returning just a stubbed out response. In this case the average message latency dropped to about 1.6-1.8 seconds. This is about what I would have expected form the addition of some extra persistence points in the orchestration.

Finally the after the external web service had been tuned a message latency of about 2-2.2 seconds was achieved.

This example suggests that lower message latency can be achieved between BizTalk and web services if the WCF adapters are used instead of the traditional SOAP adapters. We expect that we can achieve even lower message latency if we tune BizTalk for low latency as suggested by Thiago in comments to Zeeshan’s article.

Receive port	Send Port	Loadgen testmessages/sec	Average Request- Response Latency (seconds)
WCF-Basic HTTP Request-Response	File Adapter	10	0.8-1.0
WCF-Basic HTTP Request-Response	File Adapter with outbound maps	10	0.8-1.0
WCF-Basic HTTP Request-Response	WCF-Basic HTTP Request-Response with outbound and inbound maps to external web service	10	10-11
WCF-Basic HTTP Request-Response	WCF-Basic HTTP Request-Response with outbound and inbound maps to external web service returning a stubbed out response	10	1.6-1.8
MOSS WCF-Basic HTTP Request-Response	WCF-Basic HTTP Request-Response with outbound and inbound maps to tuned external web service	10	2.0-2.2

The SQL Adapter and Apostrophes

I got an amusing call from a DBA the other day saying that the BizTalk SQL adapter was failing whenever he sent through a message that had an apostrophe in it. The message being sent to the SQL adapter contained;

Mum’s Apple Pie

The message had suspended in Biztalk with a failure to due a failed MSDTC transaction on the SQL port. The DBA had opened the message in the BizTalk Adminstrators Console Group Hub and then copied the message into his SQL analyzer to test the SQL query. He had noticed that the apostrophe was not escaped and rightly deduced that the SQL query would fail if it was not escaped. See http://www.kamath.com/codelibrary/cl003_apostrophe.asp for example.

I told him to calm down because this was not the source of his problem. I got him to start SQL profile and set up a filter to  trace the SQL query coming from the faulting SQL adapter. Sure enough the trace showed that when BizTalk sends the same message to a SQL adapter it magically escapes the apostrophe and the SQL request now contains;

Mum’’s Apple Pie

The point here is that if you are troubleshooting a SQL adapter failure sometimes the suspended message does contain the exact SQL request that was submitted. I think that you need to trace the SQL transaction to see what is really being submitted.

Once he submitted the SQL from the trace to his stored procedure he could see that the error was due to a change he had made and it was not the fault of the BizTalk server solution. To finish with I can’t resist giving this link to an amusing cartoon about misuse of the apostrophe ( http://www.angryflower.com/bobsqu.gif ). Maybe we need a cartoon about misuse of the apostrophe in SQL queries. 

Custom Party Resolution BizTalk Server 2006: Resolve party by POP3.From and square brackets

Recently I wrote a BizTalk Server 2006 custom pipeline component to resolve a custom party on the POP3.From context value when an email was received. I was surprised to find some unusual behaviour if the POP3.From context value contained square brackets like “HEWHOCARES [WORLD]” < HEWHOCARES@world.com >. I describe the behaviour and why it occurs below because it took me by surprise.

The base example for the custom pipeline component is based on http://msdn.microsoft.com/en-us/library/aa559134.aspx . The code fragment below shows the important changes;

/// This function gets called after the entire stream of the message has been read, guaranteeing that all promoted properties
        /// from the message have been populated to the message context.
        /// </summary>
        internal void EndOfStream()
        {
            string FromEmail = null;
            BizTalkParty party;

            //obtain the FromEmail string from the message
            FromEmail = (string)mBaseMessage.Context.Read(“From”, “http://schemas.microsoft.com/BizTalk/2003/pop3-properties”);

            if (FromEmail != null)
            {
                PartyResolver pr = new PartyResolver();
                party = pr.GetPartyFromAlias(“FromEmail”, “FromEmailName”, FromEmail);

                mBaseMessage.Context.Promote(“SourcePartyID”, “http://schemas.microsoft.com/BizTalk/2003/system-properties”, party.SID);
                mBaseMessage.Context.Promote(“PartyName”, “http://schemas.microsoft.com/BizTalk/2003/messagetracking-properties”, party.Name);
            }
        }

I set up my party like;

On testing this pipeline component with an email from “HEWHOCARES” < HEWHOCARES@world.com >   it resolves the party to ThePeopleWhoCare. Now if I change the party value to “HEWHOCARES [WORLD]” < HEWHOCARES@world.com > and send an email from the same email address then it resolves to Guest not ThePeopleWhocare. What is going on? If you dig a bit further you find that to resolve the party this stored procedure is used;

USE [BizTalkMgmtDb]
GO
/****** Object:  StoredProcedure [dbo].[admsvr_GetPartyByAliasNameValue]    Script Date: 03/24/2009 21:16:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[admsvr_GetPartyByAliasNameValue]
@nvcAliasName nvarchar(256),
@nvcAliasQualifier nvarchar(64),
@nvcAliasValue nvarchar(256),
@nvcSID nvarchar(256) OUTPUT,
@nvcName nvarchar(256) OUTPUT
AS
SELECT      @nvcSID = bts_party.nvcSID,
            @nvcName = bts_party.nvcName
FROM bts_party, bts_party_alias
WHERE       UPPER(bts_party_alias.nvcName) = UPPER(@nvcAliasName) AND
            UPPER(bts_party_alias.nvcQualifier) = UPPER(@nvcAliasQualifier) AND
           bts_party_alias.nvcValue LIKE @nvcAliasValue  AND
           bts_party_alias.nPartyID = bts_party.nID

The WHERE clause uses a LIKE statement for the criteria based on the passed POP3.FROM context value.  [] in a like statement is evaluated as any single character within the specified range. Thus in our case we try find a party with an FromEmail value of “HEWHOCARES W” < HEWHOCARES@world.com > (or where W” is O”, R”, L” or D”). Indeed if I now change the party value to “HEWHOCARES W” < HEWHOCARES@world.com > then “HEWHOCARES [WORLD]” < HEWHOCARES@world.com > resolves to ThePeopleWhoCare once again.

Many-to-one mappings using the Table Looping Functoid

I have found another solution to the scenario 2 that I blogged about a couple of weeks ago (“The values in the output message are an aggregation of the two input messages with one value coming from only one input message“). This solution does not use custom XSLT just the BizTalk mapper.

I found if I used the Table looping functoid instead of the looping functoid I could get the correct transformed message as well.

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;

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″ ?>

   <SKUSResponse xmlns=”BT_GET_SKUS_AS_RESULTSET“>

      <Success>

      <BT_GET_SKUS  ROWCNT=”“  />

      <ResultSets>

        <Table1 SKUID=”266277“ DESC=”330ml COLA“ SHTDESC=”330ml COLA“  UNTWGT=”600.000“ CPQ=”24“ />

           <Table1 SKUID=”266278“ DESC=”“ SHTDESC=”500ml COLA“ UNTWGT=”0.000“ CPQ=”24“ />

      </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.

The transformed message looks like;

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

     <Table1 SKUID=”266277“ DESC=”330ml COLA“ SHTDESC=”330ml COLA“ UNTWGT=”600.000“ CPQ=”24“ xmlns=”http://BT_GET_SKUS_AS_RESULTSET“ />

       <Table1 SKUID=”266278“ DESC=”“ SHTDESC=”500ml COLA“ UNTWGT=”0.000“ CPQ=”24“ xmlns=”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.