Finding all BizTalk Send and Receive Ports that Use a Pipeline (with SQL XRef Query)

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.

Uncategorized  

Leave a Reply