Authoring BizTalk Pipeline Components on 2013 R2

As many of you may know, the BizTalk Server Pipeline Component Wizard project on Codeplex only works on BizTalk 2010 and prior. More specifically – the wizard does not work with Visual Studio 2012 or 2013. This has left me copy-and-pasting from prior classes whenever I needed to author a new custom pipeline component.

Well, I was perusing the Nuget gallery in Visual Studio 2013, searching for BizTalk to see what was out there. And lo and behold, I find the Breeze BizTalk Pipeline Component Project.

When you add the Nuget package to a class library, it creates the necessary references to the BizTalk assemblies…

pcomp1

It also creates a template class that you then modify to suit your needs (MyPipelineComponent.cs). Just rename the class, change the namespace as needed, and follow the handy instructional comments at the top of the code module. (And don’t forget to sign your assembly so you can put it in the GAC!)

pcomp2

There is a single property implemented (MyProperty) so you will want to make the necessary code changes to implement your properties.

pcomp3

pcomp4

All in all, a really nice helper since the wizard project has not been carried forward. Great idea and thanks to the guys at Breeze.net for putting it out there!

WCF SQL Server Exception FIXED

When executing a stored procedure from a WCF send port (generated by the BizTalk Consume Adapter Service function in Visual Studio) I would receive the following exception:

The adapter failed to transmit message going to send port “WcfSendPort_SqlAdapterBinding_TypedProcedures_dbo_Custom” with URL “mssql://server/database?”. It will be retransmitted after the retry interval specified for this Send Port. Details:”System.Data.SqlClient.SqlException (0x80131904): The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

The stored procedure was already in place; written by a client and contains a good bit of logic. So, I had only a little leeway to make changes. I discovered the issue had to do with transactions between BizTalk and SQL Server, and a TRY/CATCH block within the stored procedure. Apparently, they don’t work well together unless you also use an explicit BEGIN/COMMIT/ROLLBACK transaction within the stored procedure.

Sure enough, when I added a BEGIN TRANSACTION before the TRY, a ROLLBACK within the CATCH, and a COMMIT at the end of the procedure, things worked correctly. I used the syntax given at this link (scroll down to Error 16).

USE Adventure Works;
GO
-- This was added to fix the error
BEGIN TRANSACTION;

BEGIN TRY
    -- The logic is here
END TRY
BEGIN CATCH
    -- The error handling is here
    SELECT 1
    
    -- This was added to fix the error
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

END CATCH;

-- This was added to fix the error
IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

ESB Exception Management Notification Service Error FIXED

I was receiving this error after installing the much-loved (haha) BizTalk ESB Exception Notification Service.

(I jest about it being much loved because these “sample” applications shipping with BizTalk 2013 R2 haven’t been maintained and tend to be a royal pain to install. But, they provide functionality that fills in gaps in the BizTalk feature set. But I digress!)

I was receiving this error message in the Event Viewer when trying to start the service:

Service cannot be started. Microsoft.Practices.ServiceLocation.ActivationException: Activation error occured while trying to get instance of type Database, key “” —> Microsoft.Practices.Unity.ResolutionFailedException: Resolution of the dependency failed, type = “Microsoft.Practices.EnterpriseLibrary.Data.Database”, name = “(none)”.
Exception occurred while: while resolving.
Exception is: InvalidOperationException – The type Database cannot be constructed. You must configure the container to supply this value.

Fortunately, it didn’t take long to find the solution on the MSDN forum (via Bing…shameless plug/admission).

Quoting the post on the forum thread (here’s the link)…

There are two issues with this service to be fixed after the installation to make it work. Assuming the service has been installed in the default folder “C:\Program Files (x86)\Microsoft BizTalk ESB Toolkit\Exception Notification Service”, make the following changes:

1. Rename “BizTalkESBExceptionNotification.exe.config” to “BizTalk ESB Exception Notification.exe.config”

2. Open “BizTalk ESB Exception Notification.exe.config” file and replace the following line

<add name="AlertService" type="Microsoft.Practices.EnterpriseLibrary.Common.Configuration.FileConfigurationSource, Microsoft.Practices.EnterpriseLibrary.Common, Version=5.0.414.0,Culture=neutral, PublicKeyToken=31bf3856ad364e35" filePath="BizTalk ESB Exception Notification.exe.config"/>

With

<add name="AlertService" type="Microsoft.Practices.EnterpriseLibrary.Common.Configuration.FileConfigurationSource, Microsoft.Practices.EnterpriseLibrary.Common, Version=5.0.414.0,Culture=neutral, PublicKeyToken=31bf3856ad364e35" filePath="C:\Program Files (x86)\Microsoft BizTalk ESB Toolkit\Exception Notification Service\BizTalk ESB Exception Notification.exe.config"/>

BizTalk Error for Schemas With Hyphenated Root Node

While building a schema that was generated from a SQL Server stored procedure, I ran into this exception:

Specify a valid .NET type name for this root node. The current .NET type name of this root node is invalid (it is a reserved BizTalk Keyword or is an invalid C# identifier).

It turned out that the issue was the root nodes created for the typed procedure contained hyphens (because the stored procedure name contained hyphens). C# class names disallow the use of hyphens.

The solution was to open the properties dialog box for the root node (in this case there were two root nodes: one for the request and another for the response) and edit the RootNode TypeName property to remove the hyphens. This fixed the issue.

I found this solution on the MSDN forums here:

https://social.msdn.microsoft.com/Forums/en-US/04a80789-6073-453b-b531-2188416e45af/biztalk-problem-with-schema-elements-which-have-hyphens?forum=biztalkgeneral

Query String Parameters in the BizTalk REST / WCF-WebHTTP Adapter

I was banging my head against my keyboard (metaphorically, but almost literally) trying to get query string parameters to work properly with the WCF-WebHTTP Adapter in BizTalk 2013 R2. I needed to call a REST service using query string parameters in the URL. Using “?param1=val&param2=val” notation when configuring the Send Port was not working.

Fortunately, our good friend in the integration community, Richard Seroter, has encountered this before and blogged about it! Here’s the link.

http://seroter.wordpress.com/2013/03/19/yes-richard-you-can-use-ampersands-on-the-biztalk-rest-adapter-and-some-asp-net-web-api-tips/

The gist is that you have to use the HTML encoded value for the ampersands such as “?param1=val&amp;param2=val”.

Using the tips in this blog post got me up and running. Thanks Richard!

XPath to return default value if node not present

I had to put this into use today in a BizTalk map (inline XSLT). Since this is in BizTalk, I was limited to XPath 1.0 as a solution.

http://stackoverflow.com/questions/4489976/xpath-to-return-default-value-if-node-not-present

This is the problem/solution from StackOverflow:

Problem

Say I have a pair of XML documents

<Foo>
<Bar/>
<Baz>mystring</Baz>
</Foo>

and

<Foo>
<Bar/>
</Foo>

I want an XPath (Version 1.0 only) that returns “mystring” for the first document and “not-found” for the second.

Solution

In XPath 1.0, use:

concat(/Foo/Baz, substring(‘not-found’, 1 div not(/Foo/Baz)))

If you want to handle the posible empty Baz element, use:

concat(/Foo/Baz, substring(‘not-found’, 1 div not(/Foo/Baz[node()])))

With this input:

<Foo>
<Baz/>
</Foo>

Result: not-found string data type.

This worked beautifully!

Caching Values from the SSO Configuration Store

Most of us who have been using BizTalk Server for a while use the SSO database as a secure store of configuration values. Microsoft made this much easier a few years ago when they released the SSO Configuration Application MMC Snap-In. Better yet, it comes with a sample class you can use to implement in your applications. This frees developers from having to add AppConfig segments to the BizTalk config file. In multi-server deployments, this gets really nasty because you have to change the config on every application server. SSO is by default available to the cluster. So, configuration changes are automatically made available to the entire BizTalk Group.

One common use of the SSO Config Store is a repository for database connection strings required by BizTalk maps performing database look-ups. A script component can call out to a class which wraps the client code that ships with the MMC Snap-In. Or you could use the BizTalk Mapper Extensions created by Sandro Pereira, which ship with a custom functoid to SSO config.

However, there is a problem. When you use the tool in a looping BizTalk map, it will make a call to the SSO database to retrieve config values for each element in your loop. In the case of a connection string, you are making unnecessary calls to SSO. Just open up the SQL Server Profiler, and you can watch all of the extra calls to the SSO database. With large messages, I have seen this severely degrade performance.

I have opted for creating a simple wrapper method, which uses a module-level dictionary object to cache configuration values needed by the map. This reduces the number of calls to the SSO database to one per config value. As you might expect, this significantly increases performance with even moderately large messages. The durability of the cache is only for the life of a single instance of the map. So, if you want something even more durable, you would need to rely on something like the AppFabric Cache.

Here is some sample code for caching values from the SSO config store…

private Dictionary<string, string> _ssoConfigurationValues = new Dictionary<string, string>();

public string GetSSOConfiguration(string appName, string propName)
{
try
{
string key = string.Concat(appName, propName);
string value = string.Empty;

if (_ssoConfigurationValues.ContainsKey(key))
{
return _ssoConfigurationValues[key];
}
else
{
value = SSO.Utility.SSOClientHelper.Read(appName, propName);
if(!_ssoConfigurationValues.ContainsKey(key))
_ssoConfigurationValues.Add(key, value);
return value;
}

}
catch (Exception ex)
{
throw new ApplicationException("GetSSOConfiguration function call read failed", ex);
}
}

Insert XML Elements with the BizTalk Rules Engine

I have a case where I need to validate an incoming XML message and generate an aggregated XML message containing all validation failures. The base functionality of the BRE does not allow this kind of message manipulation – adding new elements (nodes) to messages and then setting the value.

My good friend Bing to the rescue!

This blog post gives a good walkthrough of doing exactly what I needed to do using the Microsoft.RuleEngine.XMLHelper class. Pay close attention to the Windows Registry setting at the end of the post.

http://arnulfowing.blogspot.com/2007/09/microsoftruleenginexmlhelper.html

This MSDN blog had a very good sample BRE Policy that I used to see a concrete example. Download the .exe and extract it.

http://blogs.msdn.com/b/biztalkbre/archive/2006/08/31/734291.aspx

This MSDN document details the Windows Registry setting that I mentioned previously. The function is to allow the BRE to use .NET helper methods as static objects, rather than requiring an instance.

http://msdn.microsoft.com/en-us/library/aa950269.aspx

These three sources combined gave me the complete solution. It works like a charm, and I did not have to write my own custom .NET helper class to do the XML element insertion.

Good times!

Handling “OR” Operators in the BizTalk Rules Engine

Once again, here we are talking about the BRE!

I have a set of rules that utilize the OR operator. I found a very, very interesting article describing in detail how the BRE handles the OR operator.

http://geekswithblogs.net/cyoung/archive/2006/05/24/79500.aspx

Cliff Notes:

  • OR is not handled like procedural languages, such as C# – there is no short-circuiting. All elements of the OR condition are evaluated, and therefore each submits a separate match to the Agenda to be followed by the BRE
  • In the BRE (or any Rete engine) OR is much more analogous to SQL. Since each member of an OR condition results in its own “record” per-se, it can be loosely understood as a SQL using the UNION ALL clause, having a separate SQL statement for each member of the OR condition
  • BizTalk BRE performs a de-duplication after processing rules that keeps the multiple Rete results from rules using OR from being added to the processing Agenda. This mitigates some of the, “What the…” you felt when reading bullet one above!
  • If you purposely want separate Agenda items to be processed, you can force the BRE to do this by creating a separate rule for each member of your OR condition (i.e. don’t use OR in your rules, create multiple rules instead).