Could not load assembly “Microsoft.BizTalk.Interop.SSOClient, Ver=5.0.1.0” after BTS2013 upgrade

After successfully upgrading an existing BTS2010 and SSO installation to BTS2013 we found a custom pipeline component failing with the following error in the event log after some initial testing.

image

After reviewing the component source code, a reference to the Microsoft.BizTalk.Interop.SSOClient v5.0.1.0 assembly was found. Instead of recompiling the the solution using VS2012 and having to redeploying,  I decided to redirect the assembly version in the BizTalk config files BTSNTSvc.exe.config and BTSNTSvc64.exe.config.

Below is the assembly binding information that was placed between the existing <runtime> tags.

<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1" appliesTo="v4.0.30319">
   <dependentAssembly>
      <assemblyIdentity name="Microsoft.BizTalk.Interop.SSOClient"
                        publicKeyToken="31bf3856ad364e35"
                        culture="neutral" />
      <bindingRedirect oldVersion="5.0.1.0"
                       newVersion="7.0.2300.0"/>
   </dependentAssembly>
</assemblyBinding>

See this link http://msdn.microsoft.com/en-us/library/7wd6ex19.aspx for more information on redirecting assembly versions.

 

Enjoy.

In-place upgrading of BTS2010 to BTS2013 missing DB permissions

After completing an in-place upgrade of BTS2010 to BTS2013 successfully, I found the ESB Portal no longer worked and displayed the following error:

image

Also some adaptors where failing with the following error:

The type initializer for ‘Microsoft.BizTalk.Adaptotrs.ODBC.Runtime.ODBCTransmitAdaptor.ODBCAdapterProperties’ threw an exeception.

The issue is when upgrading to BTS2013 it creates 2 new tables, bts_dynamic_sendport_handlers and bts_dynamicport_subids in the BizTalkMgmtDb. However these new tables do not have any permissions applied to allow the BTS roles to access them.

After the upgrade you will need to execute the following T-SQL script to apply the correct object permissions.

use BizTalkMgmtDb
go
GRANT DELETE ON [bts_dynamic_sendport_handlers] TO [BTS_ADMIN_USERS] AS [dbo]
GO
GRANT INSERT ON [bts_dynamic_sendport_handlers] TO [BTS_ADMIN_USERS] AS [dbo]
GO
GRANT SELECT ON [bts_dynamic_sendport_handlers] TO [BTS_ADMIN_USERS] AS [dbo]
GO
GRANT UPDATE ON [bts_dynamic_sendport_handlers] TO [BTS_ADMIN_USERS] AS [dbo]
GO
GRANT SELECT ON [bts_dynamic_sendport_handlers] TO [BTS_HOST_USERS] AS [dbo]
GO
GRANT SELECT ON [bts_dynamic_sendport_handlers] TO [BTS_OPERATORS] AS [dbo]
GO
GRANT DELETE ON [bts_dynamicport_subids] TO [BTS_ADMIN_USERS] AS [dbo]
GO
GRANT INSERT ON [bts_dynamicport_subids] TO [BTS_ADMIN_USERS] AS [dbo]
GO
GRANT SELECT ON [bts_dynamicport_subids] TO [BTS_ADMIN_USERS] AS [dbo]
GO
GRANT UPDATE ON [bts_dynamicport_subids] TO [BTS_ADMIN_USERS] AS [dbo]
GO
GRANT SELECT ON [bts_dynamicport_subids] TO [BTS_HOST_USERS] AS [dbo]
GO
GRANT SELECT ON [bts_dynamicport_subids] TO [BTS_OPERATORS] AS [dbo]
GO

Enjoy.

Since then Microsoft has released a patch to fix this issue: http://support.microsoft.com/kb/2832136

Truncating data in the EsbException database

Once you got the BizTalk ESB Toolkit running for a while, you would have noticed the EsbException database will begin to grow in size if left unchecked. The main culprit is the MessageData table which holds the entire message receive by BizTalk.

Unfortunately there is no out of the box maintenance script to remove old records from this database. To resolve this issue I decided to write the stored procedure below and execute it every night in a SQL Agent job.

The parameter @DaysToKeep defines how many days worth of exception data you wish to keep. Also I decided to batch delete the rows as not to blow out the transaction log as this database can get quite large. At the end of the script I also shrink the database.

If you wish to deploy this script in a production environment, please test it thoroughly.

USE [EsbExceptionDb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*********************************************************************************************
Description:    Deletes records from the tables in a batch style and shrinks the database.

Params:            @DaysToKeep - number of days from the current date to keep alerts.
*********************************************************************************************/
ALTER procedure [dbo].[maint_TruncateOldAlerts]

@DaysToKeep int = 7

as

declare @error int, @rowcount int
declare @DateToDelete datetime
set  @DateToDelete = DATEADD(dd,@DaysToKeep*-1,GETDATE())
print @DateToDelete

--using the fault table as the key table for the insertion date

--dbo.ProcessedFault
print 'Deleting ProcessedFault'
while 1 = 1
begin
    
    delete top(1000) pf from  dbo.Fault f with (nolock) inner join dbo.ProcessedFault pf with (nolock)
    on f.FaultID = pf.ProcessedFaultID where f.InsertedDate < @DateToDelete
    
    If @@rowcount < 1 break
end

--dbo.MessageData
print 'Deleting MessageData'
while 1 = 1
begin
    
    delete top(1000) md from dbo.Fault f with (nolock) inner join dbo.[Message] m with (nolock) 
    on f.FaultID = m.FaultID inner join dbo.MessageData md with (nolock) 
    on m.MessageID = md.MessageID where f.InsertedDate < @DateToDelete
    
    If @@rowcount < 1 break
end

--dbo.ContextProperty
print 'Deleting ContextProperty'
while 1 = 1
begin
    
    delete top(1000) cp from dbo.Fault f with (nolock) inner join dbo.[Message] m with (nolock) 
    on f.FaultID = m.FaultID inner join dbo.ContextProperty cp with (nolock) 
    on m.MessageID = cp.MessageID where f.InsertedDate < @DateToDelete
    
    If @@rowcount < 1 break
end

--dbo.AuditLogMessageData
print 'Deleting AuditLogMessageData'
while 1 = 1
begin
    
    delete top(1000) almd from dbo.Fault f with (nolock) inner join dbo.[Message] m with (nolock) 
    on f.FaultID = m.FaultID inner join dbo.AuditLog al on al.MessageID = m.MessageID
    inner join dbo.AuditLogMessageData almd on al.AuditLogID = almd.AuditLogID
    where f.InsertedDate < @DateToDelete
    
    If @@rowcount < 1 break
end


--dbo.AuditLog
print 'Deleting AuditLog'
while 1 = 1
begin
    delete top(1000) al from  dbo.Fault f with (nolock) inner join dbo.[Message] m with (nolock) 
    on f.FaultID = m.FaultID inner join dbo.AuditLog al on al.MessageID = m.MessageID
    where f.InsertedDate < @DateToDelete

    If @@rowcount < 1 break
end

--dbo.AlertSubscriptionHistory
print 'Deleting AlertSubscriptionHistory'
while 1 = 1
begin
    delete top(1000) ash from dbo.Fault f with (nolock) inner join dbo.AlertHistory ah with (nolock)
    on f.FaultID = ah.FaultID inner join dbo.AlertSubscriptionHistory ash with (nolock)
    on ah.AlertHistoryID = ash.AlertHistoryID
    where f.InsertedDate < @DateToDelete

    If @@rowcount < 1 break
end

--dbo.AlertHistory
print 'Deleting AlertHistory'
while 1 = 1
begin
    delete top(1000) ah from dbo.Fault f with (nolock) inner join dbo.AlertHistory ah with (nolock)
    on f.FaultID = ah.FaultID where f.InsertedDate < @DateToDelete

    If @@rowcount < 1 break
end

--dbo.AlertEmail
print 'Deleting AlertEmail'
while 1 = 1
begin
    delete top(1000) ae from dbo.AlertEmail ae where ae.InsertedDate < @DateToDelete
    If @@rowcount < 1 break
end


--dbo.Message
print 'Deleting Message'
while 1 = 1
begin
    delete top(1000) m from dbo.Fault f with (nolock) inner join dbo.[Message] m with (nolock) 
    on f.FaultID = m.FaultID where f.InsertedDate < @DateToDelete

    If @@rowcount < 1 break
end

--dbo.Fault
print 'Deleting Fault'
while 1 = 1
begin
    delete top(1000) f from dbo.Fault f with (nolock) where f.InsertedDate < @DateToDelete

    If @@rowcount < 1 break
end


--shrink the database 
DBCC SHRINKDATABASE (EsbExceptionDb, 10);

go

Enjoy.

What to use SOAP or REST

Choosing between SOAP and REST style web services for an architectural solution should depend on the consumers of the service in my opinion.  To help me make the right decision I decided to draw up a comparison matrix below showing the pros and cons of each service style.

Feature SOAP REST
Development effort

Having comprehensive toolkits  make development easier.

Toolkits are not required.  However additional work is required to map URI paths the specific handlers.
Describing available Interface definitions. Generally a WSDL is available to describe the available contracts and by using client tools proxies maybe easily generated A document is normally manually written and is  published as a web page. There is a machine readable version called WADL but is not widely used.
Message format XML based format. Has SOAP and WS-* specific markup. This can make the payload quite large. Can craft your own however common formats are XML or JASON. Does not require XML parsing.
Human readable results.
Message Transport Can use a number of transport protocols such as HTTP/S, TCP, SMTP, UDP, JMS etc Normally HTTP/HTTPS. Other protocols are supported with extra development effort.
Message contracts SOAP requires a formal contract to exist between the provider and consumer. 
If  rigid type checking is required then use SOAP.
Focus is on accessing named operations.

Has a form of dynamic contract and relies on documentation. Focus is  on accessing named resources.

Handling of complex domain objects Complex domain models can be easily represented using soap. Not so easy to handle complex models.  Excellent choice if you only require CRUD operations  over a RDBMS.
Transactional support WS* protocol supports transactions which is geared towards SOAP. Has no built in support.  The HTTP protocol cannot provide two-phase commit across distributed transactional resources.
Reliable messaging Built into the WS-* protocol. Has built in successful/retry logic. Clients need to deal with communication failures.
State management Supports both contextual information and conversation state management. The server cannot maintain any state. State management must be handled by the client
Caching No supported HTTP Get operations can be cached.
Message Encoding Supports text and binary encoding Limited to text only
Testing of services Requires unit tests to be developed or 3 rd party test tools. Can simply use a web browser and view the results. 
Security Supports enterprise security  using the WS-Security protocol. Use SOAP if intermediary devices are not trusted. Use SSL protocol for point-to-point.
Also can easily identify the intent of a request based on the HTTP verb.
Client side development complexity Toolkits are required to easily consume the service. Can consumed by any client, even a web browser using Ajax and Javascript.
Maintainability Easier to maintain due to tight data contracts and standards. In the long-run can be much expensive to maintain due to lack of standards
Popularity Mainly in enterprise applications that required WS-* features. Used by most  publically  available  web services.

My conclusion is there is no right or wrong approach for building web services with either SOAP or REST, it depends on the requirements of the consumers.

I tend to lean towards REST for CRUD type web services that integrate with websites and  SOAP for integration between critical enterprise systems that require the WS-* features such as transaction support and reliable communications.

I hope anyone reading this will find this blog helpful in making the correct architectural decision and please let me know I have left anything out.

XMLTranslatorStream to the rescue

This utility class found in the Microsoft.BizTalk.Streaming namespace has saved me on many occasions where I had to modify namespaces, elements, attributes and the XML declarations inside a pipeline and from a referenced custom assemble inside an orchestration. This class uses a stream reader and writer with virtual methods representing the components of a xml structure.

More information can be found here: http://msdn.microsoft.com/en-us/library/microsoft.biztalk.streaming.xmltranslatorstream.aspx

Using the XMLtranslatorStream avoids having to load the xml message into a xmlDocument object which will impact performance for large messages.

The list below shows all the available methods that may be overridden within this class

protected override int ProcessXmlNodes(int count);
 protected virtual void TranslateAttribute();
 protected virtual void TranslateAttributes();
 protected virtual void TranslateAttributeValue(string prefix, string localName, string nsURI, string val);
 protected virtual void TranslateCData(string data);
 protected virtual void TranslateComment(string comment);
 protected virtual void TranslateDocType(string name, string pubAttr, string systemAttr, string subset);
 protected virtual void TranslateElement();
 protected virtual void TranslateEndElement(bool full);
 protected virtual void TranslateEntityRef(string name);
 protected virtual void TranslateProcessingInstruction(string target, string val);
 protected virtual void TranslateStartAttribute(string prefix, string localName, string nsURI);
 protected virtual void TranslateStartElement(string prefix, string localName, string nsURI);
 protected virtual void TranslateText(string s);
 protected virtual void TranslateWhitespace(string space);
 protected virtual void TranslateXmlDeclaration(string target, string val);
 protected virtual bool TranslateXmlNode();

 

 

To use the XmlTranslatorStream inside a custom pipeline, add a subclass that inherits the XmlTranslatorStream. An example is shown below where I have overridden some of the methods.

Extra information maybe passed to the subclass by adding parameters to the constructor.

#region Subclass XmlModifierStream
    public class XmlModifierStream : XmlTranslatorStream
    {
        public XmlModifierStream(Stream input): base(new XmlTextReader(input), Encoding.Default)
        {
            Debug.WriteLine("[BTS.Utilities.CustomPipelines.XmlNamespaceModifierStream]Entered method");
        }

        protected override void TranslateXmlDeclaration(string target, string val)
        {
            base.TranslateXmlDeclaration(target, val);            
        }

        protected override void TranslateStartElement(string prefix, string localName, string nsURI)
        {            
            base.TranslateStartElement(prefix, localName, nsURI);
        }

        protected override void TranslateStartAttribute(string prefix, string localName, string nsURI)
        {            
            base.TranslateStartAttribute(prefix, localName, nsURI);    
        }         
    }
#endregion

 

 

Then inside the Execute method of the pipeline call the constructor of the sub class passing the data stream as shown below.

public Microsoft.BizTalk.Message.Interop.IBaseMessage Execute(
          Microsoft.BizTalk.Component.Interop.IPipelineContext pc, Microsoft.BizTalk.Message.Interop.IBaseMessage inmsg)
      {
          Debug.WriteLine("[BTS.Utilities.CustomPipelines.Execute]Entered method");

          if (inmsg == null || inmsg.BodyPart == null || inmsg.BodyPart.Data == null)
          {
              throw new ArgumentNullException("inmsg");
          }

          //call the xml translator subclass
          inmsg.BodyPart.Data = new XmlModifierStream(inmsg.BodyPart.GetOriginalDataStream());

          Debug.WriteLine("[BTS.Utilities.CustomPipelines.Execute]Exit method");
          return inmsg;
      }

 

 

Below are some examples where I have used the XMLTranslatorStream class.

In this scenario the incoming message contained elements which specified the document version and document type. The values TypeVersion and Type were read from the message below and used to identify the message type and finally rename the root element, remove all the element prefixes and update the namespace.

image

Inside the Execute function of the pipeline, I first opened a stream to obtain the required values from the message. These values are then passed as parameters to the subclass XmlNamespaceModifierStream together with a reference to the original stream.

The subclass overrides the TranslateStartElement method and checks if the current element is the root element of the document. If it is the root element I rename the element from StandardBusinessDocument to either CatalogueItemNotification or PriceSynchronisationDocument and set the namespace to a new value.

The overiden TranslateAttribute removes any attributtes that are prefixed with “xmlns”

//xpath the the document version elelment

private const string XPATHQUERY_VERSION = "/*[local-name()='StandardBusinessDocument']/*[local-name()='StandardBusinessDocumentHeader']/*[local-name()='DocumentIdentification']/*[local-name()='TypeVersion']";
private const string DOCUMENTTYPE_ELEMENT = "Type";




#region IComponent members
public Microsoft.BizTalk.Message.Interop.IBaseMessage Execute(Microsoft.BizTalk.Component.Interop.IPipelineContext pc, Microsoft.BizTalk.Message.Interop.IBaseMessage inmsg)
{
Debug.WriteLine("[BTS.Utilities.CustomPipelines.NamespaceModifier.Execute]Entered method");

if (inmsg == null || inmsg.BodyPart == null || inmsg.BodyPart.Data == null)
{
throw new ArgumentNullException("inmsg");
}

string propertyVersionValue = string.Empty;
string propertyTypeValue = string.Empty;

if (!string.IsNullOrEmpty(XPATHQUERY_VERSION))
{
Debug.WriteLine("[BTS.Utilities.CustomPipelines.NamespaceModifier.Execute]Obtain the xpath value from within the document");
IBaseMessagePart bodyPart = inmsg.BodyPart;
Stream inboundStream = bodyPart.GetOriginalDataStream();

//note that the path would be "C:\Documents and Settings\<BTSHostInstanceName>\Local Settings\Temp" for the virtual stream
VirtualStream virtualStream = new VirtualStream(VirtualStream.MemoryFlag.AutoOverFlowToDisk);
ReadOnlySeekableStream readOnlySeekableStream = new ReadOnlySeekableStream(inboundStream, virtualStream);
XmlTextReader xmlTextReader = new XmlTextReader(readOnlySeekableStream);
XPathCollection xPathCollection = new XPathCollection();
XPathReader xPathReader = new XPathReader(xmlTextReader, xPathCollection);
xPathCollection.Add(XPATHQUERY_VERSION);
bool isFirstMatch = false;
while (xPathReader.ReadUntilMatch())
{
//only interested in the first match
if (xPathReader.Match(0) && !isFirstMatch)
{
propertyVersionValue = xPathReader.ReadString();
isFirstMatch = true;

//get the type next which is 2nd element down 
while (xPathReader.Read())
{
if (xPathReader.LocalName.Equals(DOCUMENTTYPE_ELEMENT))
{
propertyTypeValue = xPathReader.ReadString();
break;
}
}
}
}

if (isFirstMatch)
{
Debug.WriteLine(string.Format("[BTS.Utilities.CustomPipelines.NetNamespaceModifier.Execute]Match found for xpath query. Value equals:{0}", propertyVersionValue));
}
else
{
Trace.WriteLine(string.Format("[BTS.Utilities.CustomPipelines.NetNamespaceModifier.Execute]No match found for xpath query '{0}'", XPATHQUERY_VERSION));
}

//rewind back to start
readOnlySeekableStream.Position = 0;
bodyPart.Data = readOnlySeekableStream;
}

inmsg.BodyPart.Data = new XmlNamespaceModifierStream(inmsg.BodyPart.GetOriginalDataStream(), propertyVersionValue, propertyTypeValue);
Debug.WriteLine("[BTS.Utilities.CustomPipelines.NamespaceModifier.Execute]Exit method");
return inmsg;
}
#endregion

#region Subclass XmlNamespaceModifierStream
public class XmlNamespaceModifierStream : XmlTranslatorStream
{
private const string CIN_DOCTYPE = "catalogueItemNotification";
private const string CPN_DOCTYPE = "priceSynchronisationDocument";
private const string ROOT_GS1_ELEMENT = "StandardBusinessDocument";
private const string NS_PREFIX = "urn:ean.ucc:";

private string _newNamespaceVersion;
private string _documentType;

protected override void TranslateStartElement(string prefix, string localName, string nsURI)
{
string newNSUri = string.Empty;
bool isElementFoundWithNamespace = false;
bool isFirstElement = false;

if (!string.IsNullOrEmpty(prefix) && !isFirstElement)
{
//element found with prefix. Modify namespace with new value and append passed namespace version 
newNSUri = NS_PREFIX + _newNamespaceVersion;
isElementFoundWithNamespace = true;

if (localName.Equals(ROOT_GS1_ELEMENT))
isFirstElement = true;
}

if (isElementFoundWithNamespace & isFirstElement)
{
//replace with new namespace
Debug.WriteLine(string.Format("[BTS.Utilities.CustomPipelines.NamespaceModifier.XmlNamespaceModifierStream]Replace namespace with {0}", nsURI + newNSUri));

if (_documentType.Equals(CIN_DOCTYPE))
localName = localName + "Catalogue";
if (_documentType.Equals(CPN_DOCTYPE))
localName = localName + "Price";

base.TranslateStartElement(null, localName, newNSUri);
}
else
{
base.TranslateStartElement(null, localName, null);
}

}

protected override void TranslateAttribute()
{
if (this.m_reader.Prefix != "xmlns" && this.m_reader.Name != "xmlns")
base.TranslateAttribute();
}

public XmlNamespaceModifierStream(Stream input, string namespaceVersion, string documentType)
: base(new XmlTextReader(input), Encoding.Default)
{
Debug.WriteLine("[BTS.Utilities.CustomPipelines.NamespaceModifier.XmlNamespaceModifierStream]Entered method");
_newNamespaceVersion = namespaceVersion.Trim();
_documentType = documentType.Trim();
Debug.WriteLine("[BTS.Utilities.CustomPipelines.NamespaceModifier.XmlNamespaceModifierStream]Exit method");
}
}

#endregion

In this custom send pipeline I used the XMLTranslator to add the following XML declaration “version=”1.0″ encoding=”utf-8″” and to modify the namespaces and prefixes of some elements and attributes.

The Execute function in the custom pipeline component simply calls the subclass XmlExtensionModifierStream passing only the original message stream as a parameter value.

The subclass overrides the TranslateXmlDeclaration,  TranslateStartElement and TranslateStartAttribute methods to modify the values.

#region IComponent members
    public Microsoft.BizTalk.Message.Interop.IBaseMessage Execute(Microsoft.BizTalk.Component.Interop.IPipelineContext pc, 
             Microsoft.BizTalk.Message.Interop.IBaseMessage inmsg)
    {
        Debug.WriteLine("[BTS.Utilities.CustomPipelines.ExtensionModifier.Execute]Entered method");

        if (inmsg == null || inmsg.BodyPart == null || inmsg.BodyPart.Data == null)
        {
            throw new ArgumentNullException("inmsg");
        }

        inmsg.BodyPart.Data = new XmlExtensionModifierStream(inmsg.BodyPart.GetOriginalDataStream());        
        Debug.WriteLine("[BTS.Utilities.CustomPipelines.ExtensionModifier.Execute]Exit method");
        return inmsg;
    }
    
#endregion

#region Subclass XmlExtensionModifierStream
    public class XmlExtensionModifierStream : XmlTranslatorStream
    {
        public XmlExtensionModifierStream(Stream input)
            : base(new XmlTextReader(input), Encoding.Default)
        {
            Debug.WriteLine("[BTS.Utilities.CustomPipelines.ExtensionModifier.XmlNamespaceModifierStream]Entered method");
        }

        protected override void TranslateXmlDeclaration(string target, string val)
        {
            base.TranslateXmlDeclaration(target, val);
            this.m_writer.WriteProcessingInstruction("xml", "version=\"1.0\" encoding=\"utf-8\"");
        }

        protected override void TranslateStartElement(string prefix, string localName, string nsURI)
        {            
            switch (localName)
            {

                case "fMCGTradeItemExtension":
                    base.TranslateStartElement("fmcg", localName, "urn:ean.ucc:align:fmcg:2");
                    break;

                case "attributeValuePairExtension":
                    base.TranslateStartElement("gdsn", localName, "urn:ean.ucc:gdsn:2");
                    break;
                    
                default:
                    base.TranslateStartElement(prefix, localName, nsURI);
                    break;
            }           
        }

        protected override void TranslateStartAttribute(string prefix, string localName, string nsURI)
        {            
            switch (localName)
            {

                case "schemaLocation":
                    base.TranslateStartAttribute("xsi", localName, "http://www.w3.org/2001/XMLSchema-instance");
                    break;                

                default:
                    base.TranslateStartAttribute(prefix, localName, nsURI);
                    break;
            }
        }
         
    }
#endregion

 

 

Enjoy.

How to call a Web Service using username and password as credentials

Normally when connecting to a web service requiring username/password credentials the channel should be encrypted. BizTalk requires the channel to use HTTPS as the protocol when using BasicHttp bindings. As this was an internal web service the channel did not required any encryption.

These are the steps required to use username authentication without requiring a SSL transport.

1. Create a Custom WCF solicit-response send port as shown.

image

2. Click the Configure button and ensure the Binding Type is set to customBinding. Also check that the Binding section only contains textMessageEncoding and httpTransport as shown.

image

3. Click on textMessageEncoding and ensure you have selected the correct messageVersion. As my endpoint is an asmx web service I have chosen Soap12 as the message version.

image

4. Set the authentication scheme to match your web service by clicking on the httpTransport binding. The web service I was using required Ntlm. If you chose the incorrect authentication schema, an error will be generated in the event log when calling the web service.

Below is a screenshot of the settings for my service. Note the maxBufferSize, maxBufferPoolSize and maxReceivedMessageSize properties will need to match the web service settings also.

image

5. The username and password are then entered into the Credentials tab as shown below.

image

 

Enjoy…

EDI Mapping Dramas

NADLoop1 Segment

I had a situation where I needed to get the supplier and buyer party id’s from the EDI NADLoop1 nodes as shown

 

image

I did not want to use the looping functiod, instead I resorted to use inline XSLT with selectors as shown:

<xsl:value-of select="/s0:EFACT_D96A_ORDRSP/s0:NADLoop1/s0:NAD/NAD01[text() = 'SU']/following-sibling::s0:C082/C08201/text()" />

<xsl:value-of select="/s0:EFACT_D96A_ORDRSP/s0:NADLoop1/s0:NAD/NAD01[text() = 'BY']/following-sibling::s0:C082/C08201/text()" />

 

The selectors are represented by NAD01[text() = ‘SU’] and NAD01[text() = ‘BY’] and then by using the “following-sibling” function to get the actual party id.

Now I wanted to access these values in some C-sharp code inside a scripting functiod. Here are the steps I used.

1. Add a scripting functiod for Inline C# with no connections to any of the elements as shown below:

image

In the Inline script buffer text box I added the following code to declare the variables and functions to set the get the variable values.

private string _senderGLNNumber;
       private string _customerGLNNumber;
       private string _senderOrgType;
       private string _customerOrgType;
        
       public string SenderGLNNumberSet( string value ) { _senderGLNNumber  = value; return value;}
       public string SenderOrgTypeSet(string value) {_senderOrgType = value; return value;}

       public string CustomerGLNNumberSet( string value ) {_customerGLNNumber  = value; return value;}
       public string CustomerOrgTypeSet(string value) {_customerOrgType = value; return value;}

 

2. I then set the values using the another Scripting functiod for Inline XSLT as shown below. The output of the functiod is linked to an element in the destination schema to force the functiod to execute at the start of the transformation.

image

The Inline script buffer has the following XSLT. I use the XSLT Variable  element to call the C# functions to set the values.

<xsl:variable name="var:SenderGLNNumber" select="userCSharp:SenderGLNNumberSet(string(s0:NADLoop1/s0:NAD/NAD01[text() = 'SU']/following-sibling::s0:C082/C08201/text()))" />
  <xsl:variable name="var:CustomerGLNNumber" select="userCSharp:CustomerGLNNumberSet(string(s0:NADLoop1/s0:NAD/NAD01[text() = 'BY']/following-sibling::s0:C082/C08201/text()))" />

  <xsl:variable name="var:SenderOrgType" select="userCSharp:SenderOrgTypeSet(string(s0:NADLoop1/s0:NAD/NAD01[text() = 'SU']/following-sibling::s0:C082/C08203/text()))" />
  <xsl:variable name="var:CustomerOrgType" select="userCSharp:CustomerOrgTypeSet(string(s0:NADLoop1/s0:NAD/NAD01[text() = 'BY']/following-sibling::s0:C082/C08203/text()))" />

 

3. To set a destination element, add a Inline XSLT functiod as shown

image

The Inline script buffer has the following XSLT to create the element and value.

<xsl:element name ="RECIPIENT">
        <xsl:value-of select="userCSharp:CustomerGLNNumberGet()" />
      </xsl:element>

 

or to get it via C# code, you can use the Scripting functiod using Inline C# and adding  a retrieval function  in the Inline script buffer as shown below.

public string CustomerGLNNumberGet1() {return  _customerGLNNumber; }

 

How to hide referenced elements in a schema

When working with complex schemas it can be difficult to maintain due to the size. It would be nice to only view the elements that are not referenced.

For example you may have a schema similar to the one below but more complex than this one shown:

image

The elements Address, Person and Phone are all referenced elements and are also shown in the schema. Wouldn’t it be nice to hide those referenced elements?

Here is how to do it. Right click on the schema in the solution explorer window and open it using the XML (Text) editor as shown below:

image

Add the following declaration under the schema element

<xs:schema xmlns="http://Reference_Schema.Contact" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" targetNamespace="http://Reference_Schema.Contact" xmlns:xs="http://www.w3.org/2001/XMLSchema">


  <xs:annotation xmlns:b="http://schemas.microsoft.com/BizTalk/2003">

    <xs:appinfo>

      <b:schemaInfo root_reference="Clients"  displayroot_reference="Clients" >

      </b:schemaInfo>

    </xs:appinfo>

  </xs:annotation>

The two key attributes are root_reference and  displayroot_reference. These should be set to the root element of the schema, in this case our sample schema’s root element is called Clients.

The final schema will now look like this below after inserting the above declaration in the schema. Notice the referenced elements are not shown.

image

Here is the full xsd document below.

<?xml version="1.0" encoding="utf-16"?>

<xs:schema xmlns="http://Reference_Schema.Contact" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" targetNamespace="http://Reference_Schema.Contact" xmlns:xs="http://www.w3.org/2001/XMLSchema">


  <xs:annotation xmlns:b="http://schemas.microsoft.com/BizTalk/2003">

    <xs:appinfo>

      <b:schemaInfo root_reference="Clients"  displayroot_reference="Clients" >

      </b:schemaInfo>

    </xs:appinfo>

  </xs:annotation>


  <xs:element name="Clients">

    <xs:complexType>

      <xs:sequence>

        <xs:element name="Contacts">

          <xs:complexType>

            <xs:sequence>

              <xs:element name="Contact">

                <xs:complexType>

                  <xs:sequence>

                    <xs:element ref="Person" />

                    <xs:element ref="Address" />

                    <xs:element ref="Phone" />

                  </xs:sequence>

                </xs:complexType>

              </xs:element>

            </xs:sequence>

          </xs:complexType>

        </xs:element>

      </xs:sequence>

    </xs:complexType>

  </xs:element>

  <xs:element name="Address">

    <xs:complexType>

      <xs:sequence>

        <xs:element name="Address1" type="xs:string" />

        <xs:element name="Addess2" type="xs:string" />

        <xs:element name="Address3" type="xs:string" />

      </xs:sequence>

    </xs:complexType>

  </xs:element>

  <xs:element name="Person">

    <xs:complexType>

      <xs:sequence>

        <xs:element name="FirstName" type="xs:string" />

        <xs:element name="Lastname" type="xs:string" />

      </xs:sequence>

    </xs:complexType>

  </xs:element>

  <xs:element name="Phone">

    <xs:complexType>

      <xs:sequence>

        <xs:element name="Home" type="xs:string" />

        <xs:element name="Mobile" type="xs:string" />

        <xs:element name="Business" type="xs:string" />

      </xs:sequence>

    </xs:complexType>

  </xs:element>

</xs:schema>

 

Enjoy.

Using SQL2008 Data Table Type & BizTalk to insert parent-child rows

I wanted a way to insert a parent row with multiple child rows in one single transaction into a database using BizTalk 2010.

The solution I came up with uses the table data type in SQL 2008 to insert parent-child relationship records and the BizTalk WCF SQL Adaptor. The data I will be inserting will consist of a Sales Order header record with several sales item lines.

Below are the steps I used to accomplish this.

1. Create the tables for the Sales header record and the Sales Item records.

CREATE TABLE [dbo].[SalesHeader]
(
    [SalesHeader] [int] IDENTITY(1,1) NOT NULL,
    [OrderNumb] [nvarchar](50) NOT NULL,
    [OrderDate] [datetime] NOT NULL
)

CREATE TABLE [dbo].[SalesItem]
(
    [SaleItem] [int] IDENTITY(1,1) NOT NULL,
    [SalesHeaderID] [int] NOT NULL,
    [ItemCode] [nvarchar](255) NOT NULL,
    [Qty] [int] NOT NULL,
    [Price] [money] NOT NULL
)

go
--add the FK Cconstraint
ALTER TABLE [dbo].[SalesItem]  WITH CHECK ADD  CONSTRAINT [FK_SalesItem_SalesHeader] FOREIGN KEY([SalesHeaderID])
REFERENCES [dbo].[SalesHeader] ([SalesHeader])
ALTER TABLE [dbo].[SalesItem] CHECK CONSTRAINT [FK_SalesItem_SalesHeader]
go

2. Create the table data types in SQL. One for the header record and another for the order lines. These new table types in SQL2008 allow you to pass multiple rows in as a single parameter. In the old  days you would have to pass the data in as a XML document or use updategrams.

CREATE TYPE [dbo].[SalesHeaderTableType] AS TABLE(
    [OrderNumb] [nvarchar](255) NULL,
    [OrderDate] [datetime] NULL
)
go
CREATE TYPE [dbo].[SalesItemTableType] AS TABLE(
    [ItemCode] [nvarchar](255) NULL,
    [Qty] [int] NULL,
    [Price] [money] NULL
)
go

3. Create the stored procedure to insert the data into the tables using the table data types as the input parameters. Also I am using the scope_identity function to get the parent header id. Note table parameters can only be used as read only.

CREATE procedure [dbo].[InsertSalesOrder]
(
    @SalesHeader as dbo.SalesHeaderTableType READONLY,
    @SalesItem as dbo.SalesItemTableType READONLY
)
as

declare @SalesHeaderID int

--add the sales header record
insert into dbo.SalesHeader(OrderNumb,OrderDate)
select OrderNumb, OrderDate from @SalesHeader
select @SalesHeaderID = scope_identity()

--add the line items
insert into dbo.SalesItem (SalesHeaderID,ItemCode,Qty,Price)
select @SalesHeaderID,ItemCode,Qty,Price from @SalesItem

return @SalesHeaderID
go

4. The next step is to generate the schema for the stored procedure created above using the “Consume Adapter Service” wizard.

image

This will create the following 3 files Sales.DataSetSchema.xsd, Sales.Procedure.dbo.xsd and Sales.TableType.dbo.xsd

5. Create the schema for the input data which is just a simple Header – Detail  data structure as shown below. Note the Detail node ‘Max Occurs’ is set to unbound as there can be multiple detail line items.

image

6. Create map from the input data schema to the generated schema for the stored procedure. The functiod in the map connecting the Detail node to the SaleItemTableType is just a Looping shape.

image

7. Create a simple orchestration to write the sales data to the database. This orchestration just simply picks up the xml sales data from a pickup folder and inserts the data using a static resquest/response port. The response is simply the identity id returned from the stored procedure.  The response is then written to a drop folder as xml.

image

8. Compile and deploy the application to BizTalk. The request/response port is configured as a WCF SQL.

image

The SOAP Action header of the WCF SQL Transport Properties are configured as below

image

That’s pretty much it. Just drop a test message and check the tables have been populated.

Below is the results after dropping the test message in the pickup folder a few times.

image

 

Enjoy.

Writing to the Windows Event Log from a BizTalk Orchestration.

The following code writes a message to the windows event application log under the existing event source “BizTalk Server”.

System.Diagnostics.EventLog.WriteEntry("BizTalk Server","This is the error message",System.Diagnostics.EventLogEntryType.Error,0);

 

The code above is normally placed inside an Expression shape in the orchestration as shown as “Write eventlog”

image

However if you require to write to your own defined event source, you must create the key in the registry first under the following path “HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\eventlog\Application”. The BizTalk host account must have read access to the security key and Read/Write access the new event key.

I tend to create a new event source for each application I deploy, so I decided to use a PowerShell script to create the key and set the permissions for each of the environments to save time.

Use the script below and replace the 2 variables values $EventName and $AccountName with the name of your event source and the account of the host instance for the orchestration.

#Define variables 

[string]$EventName = "BizTalkTest2"

[string]$AccountName = "vm-tpdev01\zx_BTHost1Svc"


#Create the new key

$keyName = "HKLM:\SYSTEM\CurrentControlSet\services\eventlog\Application\" + $EventName

md $keyName


#set the permission

$acl = Get-Acl $keyName


# grant service full control to this key

$person = [System.Security.Principal.NTAccount]$AccountName

$access = [System.Security.AccessControl.RegistryRights]"FullControl"

$inheritance = [System.Security.AccessControl.InheritanceFlags]"None"

$propagation = [System.Security.AccessControl.PropagationFlags]"None"

$type = [System.Security.AccessControl.AccessControlType]"Allow"

$rule = New-Object System.Security.AccessControl.RegistryAccessRule($person,$access,$inheritance,$propagation,$type)

$acl.AddAccessRule($rule)

Set-Acl $keyName $acl


#Grant read-only to BTS Host account

$acl = Get-Acl HKLM:\SYSTEM\CurrentControlSet\services\eventlog\Security

$person = [System.Security.Principal.NTAccount]$AccountName

$access = [System.Security.AccessControl.RegistryRights]"ReadKey"

$inheritance = [System.Security.AccessControl.InheritanceFlags]"None"

$propagation = [System.Security.AccessControl.PropagationFlags]"None"

$type = [System.Security.AccessControl.AccessControlType]"Allow"

$rule = New-Object System.Security.AccessControl.RegistryAccessRule($person,$access,$inheritance,$propagation,$type)

$acl.AddAccessRule($rule)

Set-Acl HKLM:\SYSTEM\CurrentControlSet\services\eventlog\Security $acl


#Check permissions

get-acl  $keyName | Format-Table -wrap

get-acl HKLM:\SYSTEM\CurrentControlSet\services\eventlog\Security | Format-Table -wrap




Next open a PowerShell window as “Run as Administrator” and execute the script to create the key and permissions.

Enjoy.