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
Advertisements

2 thoughts on “Extracting orchestration debugger’s tracked events

  1. Hello thx for the info, but do you have it on C#. If so please would you post it. And other question what does mean t.c.value. I put the script over my sql and it not recogniced it. Thanks in advice

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s