Yossi Dahan [BizTalk]


Friday, March 23, 2007

Query HAT based on an interchange Id

This one got me wondering for while, but as ever - once you know the answer - it is quite simple really.

Initially I was struck by the fact that there's no apparent way to find anything in HAT based on the interchange id.

I've got to look at it because we're doing quite a lot of tracking in our solution and as you can imagine, the interchange id was quite an appealing thing to use to id our processes and link various tracking and tracing information to them.

However, we quickly found out that when we do have a problem, although we can quickly get from our tracking the interchange id, there's no query in HAT that would find you the orchestration instance(s) that relates to that interchange id. the interchange id seems to be in the message's context and thats it. or so we thought.

Since we track a lot of information, over time, we could take an overview of what we have again, and so we spotted that the interchange id is always the id of the first message in the interchange (at least in our case, we did not do an extensive research into this)

This mean that we could now easily write a HAT query that would find this messgage. then - looking at that message's flow we can see which pipeliens and, more importantly for us, which orchestrations processed it and even get a neat little link to open them in replay mode.

The more difficult bit is to locate instances of orchestrations that are part of that interchange but we're spawned as a result of other direct bound ports from the orchestration ( as opposed to call or start orchestration shapes), but with careful digging through HAT I do belive that is possible as well.

So - to conclude - it is possible, and actually quite easy, to find an orchestration in HAT using an interchange id.



  • Hi Yossi,

    Can you please provide an example on how to use the Interchange ID in HAT to find info about a message?


    By Anonymous Anonymous, at 04/04/2007, 08:09  

  • In HAT - create the following query in the query builder -

    declare @MessageId as nvarchar(50)
    set @MessageId = '[interchange id goes here]'

    SELECT [MessageInstance/SchemaName],[Event/Direction],dateadd(minute, UtcOffsetMin, [Event/Timestamp]) s [Timestamp], [Event/Adapter],[Event/URL],[Event/DecryptionCertificate],[Event/Signature],[ServiceInstance/InstanceID],[ServiceInstance/ActivityID],[MessageInstance/InstanceID],[Event/EventID],[MessageInstance/PartCount],[MessageInstance/Size],[Event/Party],[Event/Port]FROM [dbo].[dtav_MessageFacts] AS mf WITH (READPAST)
    WHERE [MessageInstance/InstanceID]=@MessageId ORDER BY [Event/Timestamp] desc

    insert your interchange id where marked (2nd line) and run the query to find the first message. use the message flow to find the pipelines and orchestration involved.

    By Blogger Yossi Dahan, at 04/04/2007, 08:52  

  • Yossi,

    Good work on finding this, this is very useful indeed.


    By Anonymous Anonymous, at 04/04/2007, 11:04  

  • Yossi,
    Usefull information! Thanks!

    Leonid Ganeline
    BizTalk Solution Developer
    Microsoft Certified Technology Specialist: BizTalk Server 2006

    By Anonymous Anonymous, at 05/04/2007, 16:44  

  • Hi Yossi,

    Please help in providing more information on the query you have provided here.

    I tried to use it and its not working!

    Also a related question, how do we get the exact interchangeid value to query for a simple flow involving a receive pipeline-orch-send pipeline??



    By Anonymous Anonymous, at 19/04/2007, 17:11  

  • I'm sorry, you are right and I did leave something in the query I shouldn't have. this is an updated one (but remember it is quite simple to remove or add fields and calculated fields as you need them) -

    declare @MessageId as nvarchar(50)
    set @MessageId = '{94B176B9-1503-40A5-AAC2-A8B9C908824B}'

    SELECT [MessageInstance/SchemaName],[Event/Direction], [Event/Adapter],[Event/URL],[Event/DecryptionCertificate],[Event/Signature],[ServiceInstance/InstanceID],[ServiceInstance/ActivityID],[MessageInstance/InstanceID],[Event/EventID],[MessageInstance/PartCount],[MessageInstance/Size],[Event/Party],[Event/Port]FROM [dbo].[dtav_MessageFacts] AS mf WITH (READPAST)
    WHERE [MessageInstance/InstanceID]=@MessageId ORDER BY [Event/Timestamp] desc

    How you're going to get the interchange id is really up to you. at the end of the day it is simply a context property added to the messages created by BizTalk.
    you have access to it in both pipeline components and in the orchestration through an expression shape, or even in the admin console by looking at any message's context if you have anything suspended you're looking at.

    By Blogger Yossi Dahan, at 20/04/2007, 10:36  

  • Hi Yossi,
    My Name is Yoram Dahan
    Email : yoramda@cellcom.co.il

    2 Questions:

    1 is the interchangeID is saved when we correlation a message, say we sending to a file adapter and waiting for correlated message on a file receive, is the interchange id is saved at the pipeline stage ?

    2. Say we send a message from orchestration to the messageBox and
    and this starting other orchestartion, is the inetchange id is saved ?

    By Anonymous Anonymous, at 02/12/2008, 21:12  

  • Hi Yoram

    Yes - the interchange Id should remain the same for both cases you've described (if I understood them correctly)

    Feel free to contact me if you have any issues there.


    By Blogger Yossi Dahan, at 02/12/2008, 21:21  

Post a Comment

<< Home