Do you need to “take inventory” of your BizTalk artifacts (assets)? I.e. Get a simple count or high-level summary for your BizTalk Artifacts?

For years, we’ve had tools like BizTalk Documenter (free on CodePlex), but that just works for one application. What if you walk into a client site and you want to know in high-level, executive summary terms, what is the size of the application?

The code below is a quick SQL-only solution. Some people shutter at the idea of using SQL against the BizTalk databases, but it’s fast and easy. Similar code could be written in C#, Powershell or other tools using ObjectExplorer, WMI, or Powershell plug-ins for BizTalk.

use BizTalkMgmtDB
set transaction isolation level read uncommitted  --allows dirt reads but avoids locking 
SELECT 
    APP.nvcName as AppName,
  (Select count(*) from bts_receiveport AS RP 
          where RP.nApplicationID = APP.nID 
          ) as RcvPortCount,
  (Select count(*) from adm_ReceiveLocation AS RL 
             INNER JOIN bts_receiveport AS RP2 ON RL.ReceivePortId = RP2.nID 
           where RP2.nApplicationID = APP.nID 
           ) as RcvLocCount,
  (Select count(*) from bts_Orchestration AS ORCH 
             INNER JOIN bts_assembly AS ASSEM ON ASSEM.nID = ORCH.nAssemblyID
          where ASSEM.nApplicationID = APP.nID 
          ) as OrchCount,
  (Select count(*) from bts_Pipeline AS PIPE 
             INNER JOIN bts_assembly AS ASSEM ON ASSEM.nID = PIPE.nAssemblyID
          where ASSEM.nApplicationID = APP.nID 
          ) as PipelineCount,
  (Select count(*) from bt_documentSpec as BTSCHEMA 
             INNER JOIN bts_assembly AS ASSEM ON ASSEM.nID = BTSCHEMA.AssemblyID
          where ASSEM.nApplicationID = APP.nID 
          ) as SchemaCount,
  (Select count(*) from bt_mapSpec as MAP
             INNER JOIN bts_assembly AS ASSEM ON ASSEM.nID = MAP.AssemblyID
          where ASSEM.nApplicationID = APP.nID 
          ) as MapCount
  --(Select count(*) from bts_Component AS PIPECOMP
  --           INNER JOIN bts_assembly AS ASSEM ON ASSEM.nID = PIPECOMP.nAssemblyID
  --        where ASSEM.nApplicationID = APP.nID 
  --        ) as PipelineComponentCount

FROM bts_application AS APP 
where App.IsSystem = 0 and App.nvcName not in ('BizTalk EDI Application','BizTalk Global','rosettanet') 
order by App.nvcName

Example Results

Ideas for Improvements

Run the same query on each of your environments: Dev, QA, Stage, Prod, etc… and store results in a SQL Holding Table. Then do a final query to merge the results and show which apps are in which environment, and identify when the number of artifacts is different. This would help you to compare one environment to another, for example Test to Prod.