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
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.
Filed under: Uncategorized