Extracting orchestration debugger’s tracked events

BizTalk Orchestration Debugger does not allow for copy-pasting the tracked events table. But with access to BizTalk tracking database you can export the data easily with only one SQL script. Then you can copy and paste it to Excel.

1. Open Orchestration Debugger

2. From the window title put down the service instance Id and sequence number

3. Assign those values to parameters in SQL script in SQL Server Management Studio

4. Execute the script, results are presented below

5. Copy the data

SQL script

---------------------------------------------------------------------------------------------------------
-- Open orchestration debugger and from the window title
-- "Process [completed] - Orchestration Debugger [replay] - {b2f63e38-e050-4f1e-b09f-36124a90cd20}#0"
-- extract @uidServiceInstId <-- {b2f63e38-e050-4f1e-b09f-36124a90cd20}
-- and @nSequence <-- 0
-- Pass those parameters to procedure below
---------------------------------------------------------------------------------------------------------
declare @uidServiceInstId uniqueidentifier
declare @nSequence int

-- Set 2 parameters here
set @uidServiceInstId = '{b2f63e38-e050-4f1e-b09f-36124a90cd20}'
set @nSequence = 0

------------------------------------------------------------
-- Get service id
------------------------------------------------------------
declare @uidServiceId uniqueidentifier

declare @ServiceInfo table (
	[nCallersServiceSequence] int,
	[uidCalleesServiceId] uniqueidentifier,
	[vtCallersActionId] uniqueidentifier,
	[strServiceName] nvarchar(max),
	[strAssemblyName] nvarchar(max),
	[uidCallersServiceId] uniqueidentifier,
	[uidNonVersionId] uniqueidentifier,
	[strParentServiceName] nvarchar(max)
	)

insert into @ServiceInfo(
	[nCallersServiceSequence], 
	[uidCalleesServiceId], 
	[vtCallersActionId], 
	[strServiceName], 
	[strAssemblyName], 
	[uidCallersServiceId],
	[uidNonVersionId],
	[strParentServiceName]
	)
exec dtasp_LocalCallParentServiceInfo @uidServiceInstId, @nSequence
set @uidServiceId = (select top 1 uidCalleesServiceId from @ServiceInfo)

------------------------------------------------------------
-- Read orchestration shapes
------------------------------------------------------------
declare @xymfile xml
declare @xymtable table (txtSymbol ntext)
insert @xymtable(txtSymbol)
exec dtasp_GetServiceXsymBySvcId @uidServiceId, @uidServiceInstId
set @xymfile = (select top 1 txtSymbol from @xymtable)

------------------------------------------------------------
-- Extract orchestration shapes
------------------------------------------------------------
declare @shapes table (
	[ShapeId] uniqueidentifier,
	[ShapeType] nvarchar(max),
	[ActionName] nvarchar(max)
	)

insert into @shapes (ShapeId, ShapeType, ActionName)
select 
	[ShapeID] = t.c.value('ShapeID[1]', 'uniqueidentifier'),
	[ShapeType] = t.c.value('shapeType[1]', 'nvarchar(max)'),
	[ActionName] = t.c.value('shapeText[1]', 'nvarchar(max)')
from @xymfile.nodes('//ShapeInfo') as t(c)

-- user friendly shape names
update @shapes
set ShapeType = left(ShapeType, len(ShapeType)- 5)
where [ShapeType] like '%Shape'

-- fixed orchestration action
insert into @shapes (ShapeId, ShapeType, ActionName)
values ('E211A116-CB8B-44E7-A052-0DE295AA0001', 'Orchestration', 'Initialization')

------------------------------------------------------------
-- Read orchestration actions
------------------------------------------------------------
declare @actions table (
	[vtInstructionId] uniqueidentifier,
	[nInternalSequence] int,
	[nAction] int,
	[dtTimeStamp] DateTime,
	[vtContext] nvarchar(max))

insert @actions([vtInstructionId], [nInternalSequence], [nAction], [dtTimeStamp], [vtContext])
exec dtasp_LocalCallGetActions @uidServiceInstId, @nSequence

------------------------------------------------------------
-- Join orchestration actions with shapes
------------------------------------------------------------
select 
	a.nInternalSequence [Sequence Number], 	
	case a.[nAction]
		when 1 then 'Start'
		when 2 then 'End'
	end [Start/End],
	s.ActionName [Action Name], 
	s.ShapeType [Action Type], 
	convert(varchar, a.dtTimeStamp, 121) [Date Time]
from @actions a
	left join @shapes s on s.ShapeId = a.vtInstructionId