locked
Orchestration shape start and end tracking RRS feed

  • Question

  • Hi,

    Does anyone know in which database does Biztalk store the orchestration shape start and end setting ? Its a check box in the admin console for orchestration under tracking, but I'm sure its stored somewhere in the database. I'm asking because I need to write a simple application to alert myself when there is a orchestration shape start and end tracking enabled.

    regards
    Ashley

    Thursday, March 19, 2009 6:04 AM

Answers

  • Hi,

    It is not completely clear from your description what you exactly want to do. I thought you wanted to see information about when an orchestrations start and end shape was executed. Now I think you mean you want to see if tracking is enabled for orchestration start and stop events.

    This information is stored in the table 'StaticTrackingInfo' in the biztalk management database. Unfortunately the information is stored serialized into in a binary field 'imgData' and can be deserialized in an easy way.

    There is an alternative however using ExplorerOM in C#:

    1 namespace ConsoleApplication1  
    2 {  
    3     class Program  
    4     {  
    5         static void Main(string[] args)  
    6         {  
    7             BtsCatalogExplorer explorer = new BtsCatalogExplorer();  
    8             explorer.ConnectionString = "Integrated Security=SSPI;Initial Catalog=BizTalkMgmtDb;Data Source=.";  
    9  
    10             BtsOrchestration orchestration = explorer.Applications["ApplicationX"].Orchestrations["OrchestrationY"];  
    11  
    12             if ((orchestration.Tracking & OrchestrationTrackingTypes.ServiceStartEnd) > 0)  
    13             {  
    14                 Console.WriteLine("Tracking is enabled!");  
    15             }  
    16             else  
    17             {  
    18                 Console.WriteLine("Tracking is disabled!");  
    19             }  
    20  
    21             Console.ReadLine();  
    22         }  
    23     }  
    24 }  
    25  

    HTH,

    Randal van Splunteren
    http://biztalkmessages.vansplunteren.net

    Please mark answered if this answers your question.

    Thursday, March 19, 2009 9:49 AM
    Moderator
  • thanks guys, i've found another code on BiztalkGurus, that explains how to check if orchestration shape start and end is enabled. Part of the code is below

                        foreach (BtsAssembly btsasm in btscat.Assemblies)
                        {
                            foreach (BtsOrchestration orch in btsasm.Orchestrations)
                            {
                                if (orch.Tracking != (OrchestrationTrackingTypes.ServiceStartEnd))
                                {
                                    Console.WriteLine("    " + DateTime.Now.ToLongTimeString() + " Orchestration " + orch.FullName + " with tracking (" + orch.Tracking.ToString() + ") enabled.");
                                }
                            }
                        }


    which is actually written by Thiago
    • Marked as answer by ash_ow Friday, March 20, 2009 6:50 AM
    • Edited by ash_ow Friday, March 20, 2009 7:38 AM
    Friday, March 20, 2009 6:50 AM

All replies

  • Hi,

    I think (not sure) this data is in the 'dta_ServiceInstances' table in the tracking database. This table is has a child relationship with the table 'dta_Services'. You need to figure out which records in the 'dta_ServiceInstances' table are start and end shapes.

    Be aware that it is highly unrecommended to query the biztalk databases directly. Even if you only do select statements. I think you can beter use BAM (Alerts) to implement this.
     

    HTH,

    Randal van Splunteren
    http://biztalkmessages.vansplunteren.net

    Please mark answered if this answers your question.

     

    Thursday, March 19, 2009 8:26 AM
    Moderator
  • its not those tables, I've already checked them out
    Thursday, March 19, 2009 8:42 AM
  • Hi,

    It is not completely clear from your description what you exactly want to do. I thought you wanted to see information about when an orchestrations start and end shape was executed. Now I think you mean you want to see if tracking is enabled for orchestration start and stop events.

    This information is stored in the table 'StaticTrackingInfo' in the biztalk management database. Unfortunately the information is stored serialized into in a binary field 'imgData' and can be deserialized in an easy way.

    There is an alternative however using ExplorerOM in C#:

    1 namespace ConsoleApplication1  
    2 {  
    3     class Program  
    4     {  
    5         static void Main(string[] args)  
    6         {  
    7             BtsCatalogExplorer explorer = new BtsCatalogExplorer();  
    8             explorer.ConnectionString = "Integrated Security=SSPI;Initial Catalog=BizTalkMgmtDb;Data Source=.";  
    9  
    10             BtsOrchestration orchestration = explorer.Applications["ApplicationX"].Orchestrations["OrchestrationY"];  
    11  
    12             if ((orchestration.Tracking & OrchestrationTrackingTypes.ServiceStartEnd) > 0)  
    13             {  
    14                 Console.WriteLine("Tracking is enabled!");  
    15             }  
    16             else  
    17             {  
    18                 Console.WriteLine("Tracking is disabled!");  
    19             }  
    20  
    21             Console.ReadLine();  
    22         }  
    23     }  
    24 }  
    25  

    HTH,

    Randal van Splunteren
    http://biztalkmessages.vansplunteren.net

    Please mark answered if this answers your question.

    Thursday, March 19, 2009 9:49 AM
    Moderator
  • Thanks Randal, but in your sample code, there is an error at line 12. It says "Object reference not set to an instance of an object."

    I'm no expert in using ExplorerOM but I did find out that the error is on the orchestration.Tracking statement
    Friday, March 20, 2009 3:22 AM
  • Hi Ashley,

    From what I can tell the table you are looking for is the dta_DebugTrace on the tracking database. You can use it in combination with the XML inside column txtSymbol of table dta_ServiceSymbols. This XML holds the orchestration shapes and their ids.

    If you know the instance guid of your orchestration service instance you can call the stored procedure dtasp_LocalCallGetActions like so:

    dtasp_LocalCallGetActions '{your orch service instance guid}', 0

    That will give you the entire history of your orchestration execution, but you still need the shape ids from dta_ServiceSymbols to get the shape names to link back to the guid in the vtInstructionId column.

    Please be very careful with running queries directly against the BizTalk databases. It is preferable that you to use the Health and Activity Tracking tool (HAT) and open the 'Orchestration Debugger' and see the start and end times of shape execution there.

    Regards,
    Thiago Almeida

    Thiago Almeida - http://connectedthoughts.wordpress.com
    Friday, March 20, 2009 5:08 AM
    Answerer
  • thanks guys, i've found another code on BiztalkGurus, that explains how to check if orchestration shape start and end is enabled. Part of the code is below

                        foreach (BtsAssembly btsasm in btscat.Assemblies)
                        {
                            foreach (BtsOrchestration orch in btsasm.Orchestrations)
                            {
                                if (orch.Tracking != (OrchestrationTrackingTypes.ServiceStartEnd))
                                {
                                    Console.WriteLine("    " + DateTime.Now.ToLongTimeString() + " Orchestration " + orch.FullName + " with tracking (" + orch.Tracking.ToString() + ") enabled.");
                                }
                            }
                        }


    which is actually written by Thiago
    • Marked as answer by ash_ow Friday, March 20, 2009 6:50 AM
    • Edited by ash_ow Friday, March 20, 2009 7:38 AM
    Friday, March 20, 2009 6:50 AM
  • Hi,

    The sample I provided was not intented to be a complete solution. It is just a sample that helps you write your own code. The code from biztalkgurus actually takes the same approach.

    My code isn't working on your machine because you probably haven't got an biztalk application named 'ApplicationX' and an orchestration named 'OrchestrationY'. If you replace the application and orchestration name with existing application and orchestration it will work.

    HTH,

    Randal van Splunteren
    http://biztalkmessages.vansplunteren.net

    Please mark answered if this answers your question.



    Friday, March 20, 2009 7:11 AM
    Moderator

  •  Oops, I clearly misunderstood the original question. I thought you were after the tables where the actual tracking data gets saved.
    The table that holds the tracking settings for orchestrations is StaticTrackingInfo on the BizTalkMgmtDb database, column imgData.

    It is much easier to use ExplorerOM as indicated by Randal and as you found.

    Regards,
    Thiago
    Thiago Almeida - http://connectedthoughts.wordpress.com
    Friday, March 20, 2009 8:23 PM
    Answerer