How do you migrate a BizTalk solution containing Cross Referencing Value functoids to Azure? This answers a question from one of my previous blogs.
The Problem – A BizTalk Map with Cross Referencing Functoids
While migrating some BizTalk maps into Azure I came across this very difficult example.
This highlights the functoids that convert a UOM from our system to to customers UOM. This happens for each order line on the invoice and is a many to one mapping. For example DRUM & JERRY map to DR & EA, INNER & CYCLINDER map to EA. See Cross Reference Functoids Description and Example in BizTalk Server – BizTalk Server Tutorial (biztalk-server-tutorial.com) or my previous blog to see how this works.
An Azure solution
Mike Stephenson gives a potential solution here. However, he gives little detail on how you would use this to replace XRef functionality in BizTalk map XSLT. Nevertheless, my solution below proposes to use this.
For the purpose of clarity I will only talk about setting up steps 3, 4 and 5 here.
Configuring the APIM
Firstly, creating the JSON file that represents the data in the BizTalk XRef tables. My SQL query works for my data but may be some has a better generic query that will work for everyone’s XRef data.
SELECT BDIRECT.[appValue] AS BDIRECT,
TAFEN.[appValue] AS TAFEN,
EVENT.[appValue] AS EVENT,
COUPA.[appValue] AS COUPA,
IRTCOUPA.[appValue] AS IRTCOUPA,
SARMY.[appValue] AS SARMY
FROM [BizTalkMgmtDb].[dbo].[xref_ValueXRefData] AS BDirect
LEFT JOIN [BizTalkMgmtDb].[dbo].[xref_ValueXRefData] AS TAFEN
ON BDirect.[commonValue] = TAFEN.[commonValue] AND TAFEN.[appTypeID] = 2 AND Bdirect.appTypeID = 1
LEFT JOIN [BizTalkMgmtDb].[dbo].[xref_ValueXRefData] AS EVENT
ON BDirect.[commonValue] = EVENT.[commonValue] AND EVENT.[appTypeID] = 3 AND Bdirect.appTypeID = 1
LEFT JOIN [BizTalkMgmtDb].[dbo].[xref_ValueXRefData] AS COUPA
ON BDirect.[commonValue] = COUPA.[commonValue] AND COUPA.[appTypeID] = 4 AND Bdirect.appTypeID = 1
LEFT JOIN [BizTalkMgmtDb].[dbo].[xref_ValueXRefData] AS IRTCOUPA
ON BDirect.[commonValue] = IRTCOUPA.[commonValue] AND IRTCOUPA.[appTypeID] = 5 AND Bdirect.appTypeID = 1
LEFT JOIN [BizTalkMgmtDb].[dbo].[xref_ValueXRefData] AS SARMY
ON BDirect.[commonValue] = SARMY.[commonValue] AND SARMY.[appTypeID] = 6 AND Bdirect.appTypeID = 1
WHERE TAFEN.appValue IS NOT NULL
FOR JSON PATH , ROOT('UOMS')
This json output defines the values for the uom types for all systems. Much like Mike did for his customer types in his example.
{
"UOMS": [
{
"BDIRECT": "BAG",
"TAFEN": "BG",
"EVENT": "BAG",
"COUPA": "BG",
"IRTCOUPA": "BG",
"SARMY": "BG"
},
{
"BDIRECT": "BAGBX",
"TAFEN": "CT",
"EVENT": "BAG",
"COUPA": "EA",
"IRTCOUPA": "EA",
"SARMY": "BG"
},
{
"BDIRECT": "BALL",
"TAFEN": "EA",
"EVENT": "EA",
"COUPA": "EA",
"IRTCOUPA": "EA",
"SARMY": "EA"
},
{
"BDIRECT": "BASKET",
"TAFEN": "BK",
"EVENT": "EA",
"COUPA": "EA",
"IRTCOUPA": "EA",
"SARMY": "BK"
}
]
}Secondly, creating two API’s like this similar to the APIM example I am following.
The UOM Code Data operation is a GET operation that uses the mock response feature of APIM to return a static json object. What is more we don’t to call out to an external data store. The url for my api would be:
https://[My Domain]/UOMMapping/
As well, the definition for the operation defines an example response message for the 200 OK response. The application/json content type and returns json shown above. In addition the policy is the same as Mike’s example.
<policies>
<inbound>
<base />
<mock-response status-code="200" content-type="application/json" />
</inbound>
<backend>
<base />
</backend>
<outbound>
<base />
</outbound>
<on-error>
<base />
</on-error>
</policies>Thirdly, the UOMTransformation operation needs to supply the systemName, uom and partnerSystemName as input. Then it will return the mapping object providing the value for partner system.
In short the source system name is a template parameter in the url and then the uom and the partner system value are query string values.
https://[My Domain]/UOMMapping/BDIRECT?uom=PKT&partnerSystemName=SARMYFinally my policy is different from the example I am following but use most of its code.
<policies>
<inbound>
<base />
<cache-lookup vary-by-developer="false" vary-by-developer-groups="false" must-revalidate="true" downstream-caching-type="none" caching-type="internal">
<vary-by-query-parameter>uom</vary-by-query-parameter>
</cache-lookup>
<set-backend-service base-url="https://[My Domain]" />
<rewrite-uri template="/UOMMapping" copy-unmatched-params="true" />
</inbound>
<backend>
<base />
</backend>
<outbound>
<base />
<set-variable name="systemName" value="@(context.Request.MatchedParameters.GetValueOrDefault("systemName"))" />
<set-variable name="uom" value="@(context.Request.OriginalUrl.Query.GetValueOrDefault("uom"))" />
<set-variable name="partnerSystemName" value="@(context.Request.OriginalUrl.Query.GetValueOrDefault("partnerSystemName"))" />
<set-body template="none">@{
var response = context.Response.Body.As<string>();
var systemName = (string)context.Variables["systemName"];
var partnerSystemName = (string)context.Variables["partnerSystemName"];
var uom = (string)context.Variables["uom"];
var mappings = JObject.Parse(response);
var mapping = mappings.SelectToken($"$.UOMMapping[?(@.{systemName} == '{uom}')]");
return mapping[$"{partnerSystemName}"].ToString();
}</set-body>
<cache-store duration="60" />
</outbound>
<on-error>
<base />
</on-error>
</policies>
Finally, this implements a low overhead way to modify Cross Reference mapping capability for my uom XRef mapping. Thanks Mike I owe you a beer.The XSLT reference assembly
Next we require a C# class that calls the API and returns the UOM value. Furthermore, this must be robust with retries. In addition it must be capable of calling from XSLT maps many times in quick succession.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net.Http;
using System.Text;
using System.Threading.Tasks;
namespace BidOne.Common.Helpers.XRef.UOM
{
public class UOMMapping
{
private readonly static HttpClient HttpClient = new HttpClient();
private static int maxRequestTries = 3;
public static string GetUOM(string UOMURL, string key, string OcpApimSubscriptionKey)
{
// maxRequestTries is a private class member set to 3 by default,
// optionally set via a constructor parameter (not shown)
var remainingTries = maxRequestTries;
var finalException = "";
do
{
try
{
remainingTries = remainingTries - 1;
string data = AsyncGetUOM(UOMURL, key, OcpApimSubscriptionKey).GetAwaiter().GetResult();
return data;
}
catch (Exception e)
{
finalException = e.Message;
}
}
while (remainingTries > 0);
return "UOM not found: " + finalException;
}
public static async Task<string> AsyncGetUOM(string UOMURL, string key, string OcpApimSubscriptionKey)
{
using (var request = new HttpRequestMessage(HttpMethod.Get, UOMURL))
{
request.Headers.Add(key, OcpApimSubscriptionKey);
try
{
var response = await HttpClient.SendAsync(request);
if (response.IsSuccessStatusCode)
{
//return response.Content.ReadAsStringAsync().Result;
response.EnsureSuccessStatusCode();
return await response.Content.ReadAsStringAsync();
}
if (response.StatusCode == System.Net.HttpStatusCode.Unauthorized)
{
throw new Exception("No valid API key provided.");
}
if (response.StatusCode >= System.Net.HttpStatusCode.InternalServerError)
{
throw new Exception("There is a problem with the UOMMapping service");
}
}
catch (TaskCanceledException taskException)
{
if (!taskException.CancellationToken.IsCancellationRequested)
{
throw new Exception("Timeout expired trying to reach the UOMmapping service.");
}
throw taskException;
}
catch (HttpRequestException httpException)
{
throw httpException;
}
catch (Exception ex)
{
throw ex;
}
return "UOM not found";
}
}
}
}
Refactoring the BizTalk Map
Firstly, compile the class as an assembly and deploy to the GAC. Secondly, open the map and replace the “Get Common Value” and “Get Application Value” functoids with a C# script functoid.
Thirdly, configure the API URL in a concatenate functoid. Fourthly, configure the APIM subscription key name as inputs to scripting functoid. Finally configure “External assembly” settings.
Testing this map within BizTalk yields XML with UOM’s transformed.
Conclusion
After refactoring the map to use this assembly instead of the Cross Referencing Value functoids we are now in good shape to move this map to Azure. Converting this map to XSLT and deploying to an Integration account is the first step. Secondly, deploying the assembly to an Integration account sets us up to use this map in a Logic app. However, there is one caveat, you must use a consumption Logic App. Standard Logic Apps do not support XSLT that reference assemblies yet.
I think it is remarkable that you can replace the Cross Referencing Value functoids with a service in APIM. My limited testing of maps in BizTalk found the run times to be better or the same. I am not suggesting you should do this on a BizTalk server though. In no doubt this is due to the caching enabled in the APIM policy.
Finally I wish to thank Mike Stephenson for publishing his blog article that started me on this journey. I hope some else might find this useful too.
