Suppose you walk into a new company, they don’t have documentation, and they use a lot of content-based routing. The first thing I do is usually draw a Visio diagram. There never really been one widely adopted standard for how to draw such diagrams. The secret is to pack the most useful information that you can in the smallest space.

When drawing BizTalk diagrams, I like to keep everything one page if possible. Of course, sometimes you have to break on multiples pages, and when I do, I don’t like cross-page lines or pointers, if they can be avoided.

In BizTalk, a Send Port or a SendPort Group can subscribe to the data coming in from a Receive Port (and of course one Receive Port can have multiple Receive Locations). The subscription however, is by the ReceivePortName, not the ReceiveLocationName. You can have multiple send ports subscribe to the same message (thus the name Pub-Sub, short for Publisher/Subscriber model, upon which BizTalk is based).

To get to the above screen, I found a SendPort in BizTalk Admin Console, right clicked on it, then selected “Filters” on the left side. The above has one simple filter. All messages received from a ReceivePortName of MSMQDemo_RcfFromMSMQ_biztalktrans will be routed to this SendPort. You can add and change the filter/subscriptions on the top part of the screen. For convenience, the filters are shown at the bottom (and you can copy/paste them from there). It’s hard to see the entire names on the top, because of the limited width of the columns.

So often you can use business logic, naming convention, and and guessing to determine which SendPorts subscript to which ReceivePorts. Or you can open each SendPort and write them down, or copy/paste the above filters into a Word Doc, Spreadsheet, or Visio diagram.

But all the data in BizTalk Admin Console is stored in one BizTalk’s SQL Databases (BizTalkMgmtDB). The query below allows you find all SendPorts subscribing to a ReceivePort.

The code below was original found here: gallery.technet.microsoft.com/scriptcenter.

use BizTalkMgmtDb
go  -- If you have the "use" above, without the "GO" here you will get an error on the "With" statement
WITH
TmpXMLNode
  ( SendPortName, ApplicationName, tempXMLcolumn )
AS
  (SELECT
    SP.nvcName AS SendPortName
    , APP.nvcName AS ApplicationName
    , CAST(REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(MAX), SP.nvcFilter),'>','>'),'<','<'),
      'xmlns="http://www.w3.org/2001/XMLSchema-instance"','') AS XML) AS tempXMLcolumn
  FROM
    bts_sendport AS SP
    INNER JOIN bts_application AS APP ON SP.nApplicationID = APP.nID
  WHERE
    CONVERT(VARCHAR(MAX), nvcFilter) <> ''
  )

SELECT 
  SendPortName,
  ApplicationName,
  CONVERT(VARCHAR(255), nref.query('data(@Value)')) AS FilterValue,
  CONVERT(VARCHAR(255), nref.query('data(@Property)')) AS FilterProperty,
  CONVERT(VARCHAR(255), nref.query('data(@Operator)')) AS FilterOperator
FROM
  TmpXMLNode
  CROSS APPLY
    TmpXMLNode.tempXMLcolumn.nodes('/Filter/Group/Statement') AS R(nref)
WHERE
  CONVERT(VARCHAR(255), nref.query('data(@Property)')) = 
     'BTS.ReceivePortName' -- filter type  (do not change the value in quotes) 
  AND CONVERT(VARCHAR(255), nref.query('data(@Value)')) = 
     'YourRcvPortHere'  -- change this value in quotes  

You can of course put a LIKE clause on the final where clause:

  AND CONVERT(VARCHAR(255), nref.query('data(@Value)')) like 'MSMQ%' 

The receive port name is stored in the FilterValue column when the FilterProperty is set to “BTS.ReceivePortName”.

Results of query in SSMS:

You may recall that you can also filter on your own schema/promoted fields. So you could have one SendPort subscribe to PurchaseOrderAmount > 5000 and a different one subscribe to PurchaseOrderAmount <= 5000.

Note in the above query, I added the “Operator” column, which was not in the original SQL sample.

The filters above are stored in a text column called nvcFilter. Part of the trick of the SQL is convert this to an XML column, so XQuery statement can be performed on it.

So to demonstrate what is going on internally, I created a dummy SendPort with several filters just to see something beyond the most simple example.

I then ran this query

SELECT *
  FROM bts_sendport AS SP
    INNER JOIN bts_application AS APP ON SP.nApplicationID = APP.nID
  WHERE  nvcFilter like '%msmq%'

I then copied the contents of the nvcFilter column to NotePad++ and formatted it:

<Filter xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Group>
    <Statement Property="BTS.ReceivePortName" Operator="0" Value="ABC" />
    <Statement Property="MSMQ.Priority" Operator="3" Value="5" />
    <Statement Property="MSMQ.SourceMachine" Operator="0" Value="Machine1" />
  </Group>
  <Group>
    <Statement Property="BTS.ReceivePortName" Operator="0" Value="DEF" />
    <Statement Property="BTF2.commitmentRct_decidedAt" Operator="0" Value="test" />
  </Group>
  <Group>
    <Statement Property="BTF2.commitmentRct_commitmentCode" Operator="0" Value="test" />
  </Group>
</Filter>

 

So from this, we can discern that the > Operator is stored as a 3. One could continue this exercise and map each of the operators to the numbers, and then decode the numeric values back to the humanly readable values to show in the query results.

Notice also that the “and”s and “or”s are saved in separate “Group” elements.

So now, you know how to find and cross-reference all the SendPorts that are used by your ReceivePorts, and hopefully that will help you figure out content-based routing and better understand and document your system.

Jan 31, 2018 – See new related blog: BizTalk SQL to Xref Orchestration to Send/Receive Ports.

Update 03/13/2020 – I recently became aware of a tool that does this for you, it’s called
of course the “BizTalk Filter Finder Tool”