none
Fetching Message id from SQL Database RRS feed

  • Question

  • I need to get the messageId from the SQL database using the following query,

    SELECT [uidMessageInstanceId]  FROM [BizTalkDTADb].[dbo].[dta_MessageInOutEvents]

    where [uidMessageInstanceId] !='C8A74430-46C0-4F6B-A53E-88F3146743DA'

    and [uidServiceInstanceId] in
    (SELECT [uidServiceInstanceId] FROM [BizTalkDTADb].[dbo].[dta_MessageInOutEvents] where [uidMessageInstanceId] ='C8A74430-46C0-4F6B-A53E-88F3146743DA')

    Kindly advice the ways for doing it.


    Regards, Vivin.

    Tuesday, June 3, 2014 5:25 AM

Answers

  • Like I said, all the information is already there in the Tracking DB, so build your interface on top of that.

    +11  This demonstrates that all the data you need is alredy in BizTalk Tracking.  It's perfectly fine if you want to develop your own interface.  FYI, you can configure BT Admin to show only the Tracking Database.

    You should also give serious consideration to BizTalk 360.  It has much more granular access control then BT Admin and can also query the Tracking Database. 

    Here's a hint, you should follow the same pattern used by BT Admin:

    1. Query dta_MessageFieldValues with the file name
    2. Join/query dta_MessageInOutEvents to get the other uidMessageInstanceId (you mostly have this query already).
    3. Retrive the message content with the WMI MSBTS_MessageInstance class.

    • Marked as answer by Angie Xu Monday, June 9, 2014 5:20 AM
    Friday, June 6, 2014 1:58 PM

All replies

  • Hi Vivin,

    Message ID is a GUID of the message in BizTalk Server. The value comes directly from the message context property BTS.MessageID.

    So you can promote the same context property in pipeline to get its value

    Thanks

    Abhishek

    Tuesday, June 3, 2014 6:15 AM
  • I can get the MessageId by the BTS.MessageID but it returns me the wrong MessageID which has no message bodies tracked. So I need to get the other MessageID with the same service instance by passing the BTS.MessageID.

    Kindly advice how this can be done.


    Regards, Vivin.

    Tuesday, June 3, 2014 6:36 AM
  • Sorry, I don't follow.  You have the query to get the MessageID so...????

    Anyway, I'll repeat, there are better ways to keep the EDI content in the app.

    Tuesday, June 3, 2014 2:21 PM
  • I will be storing the MessageId in a separate table in oracle and later use an interface to pick the file content of a given file name whenever needed... My requirement is to view the content of the file when the file name is given.


    Regards, Vivin.

    Wednesday, June 4, 2014 5:04 AM
  • Ok, although I agree with BoatSeller that you are going down a wrong path here (A BizTalk Application should NEVER be aware of the Tracking database!), I think what you are looking for is this:

    Receive Port:

    InstanceID 1 MessageID 1 Status 0 (Message Received by Receive Port)

    InstanceID 1 MessageID 2 Status 1 (Message Sent by Receive Port)

    Orchestration:

    InstanceID 2 MessageID 2 Status 0 (Message Received by Orchestration)

    InstanceID 2 MessageID 3 Status 1 (Message Sent by Orchestration)

    So if you right now have MessageID 2 and you need MessageID 1, you can use the following query:

    select me.uidMessageInstanceID
    from
    dbo.dta_MessageInOutEvents me
    where me.uidServiceInstanceId = 
    (
    select meinner.uidServiceInstanceId 
    from dbo.dta_MessageInOutEvents meinner
    where (meinner.uidMessageInstanceId = '2') and (meinner.nStatus = 1)
    )
    and 
    me.nStatus = 0

    Morten la Cour

    Wednesday, June 4, 2014 5:19 AM
  • Thanks morten for more clearer query but my doubt is where to use the query.

    Regards, Vivin.

    Wednesday, June 4, 2014 8:03 AM
  • What are the possible ways of using this query other than a helper class??

    Regards, Vivin.

    Wednesday, June 4, 2014 8:04 AM
  • Also for the above query I have two records being returned. One from the Receive port [File Adapter] and  another from the Send port [WCF-Custom - OracleDB]. Kindly advice if there is any way to stop the messages from the send port.

    Regards, Vivin.



    • Edited by Vivin Muthu Wednesday, June 4, 2014 9:03 AM
    Wednesday, June 4, 2014 9:01 AM
  • I will be storing the MessageId in a separate table in oracle and later use an interface to pick the file content of a given file name whenever needed... My requirement is to view the content of the file when the file name is given.


    Instead of going to all that trouble with Oracle, how about using a Pipeline Component to write just the file name to a custom property, then track that property on the Port.

    You can then search directly in the BizTalk database by the filename.

    Wednesday, June 4, 2014 11:39 AM
  • That looks simple but I am new to BizTalk and do not have any experience in writing custom components. Also our requirement is to use a separate interface for getting the content of the tracked message.

    Regards, Vivin.

    Wednesday, June 4, 2014 1:51 PM
  • Well, now's a great time to learn!  Here's where the docs on Message Tracking start: http://msdn.microsoft.com/en-us/library/aa561587.aspx

    Developing a Pipeline Component is not at all difficult and you can start with the Pipeline Component Wizard: http://btsplcw.codeplex.com/

    Also worth noting, the UI for BizTalk Tracking can be setup without the Admin piece.  Of course, you can always develop a separate interface to the DTA database.  Either way, anything in Oracle would just be a duplicate of what's already in BizTalk.

    Wednesday, June 4, 2014 2:46 PM
  • Thanks boatseller!! I am unable to install the wizard since I am using BizTalk server 2013 but it is compatible only with BizTalk Server 2010.


    Regards, Vivin.

    Thursday, June 5, 2014 4:56 AM
  • Well, technically, the project is not compatible with Visual Studio 2012 but any Pipeline Component you generate in Visual Studio 2010 will work fine with VS 2012/BizTalk 2013.
    Thursday, June 5, 2014 11:27 AM
  • The problem is I am not able to install the custom pipeline component wizard.

    Regards, Vivin.

    Thursday, June 5, 2014 11:31 AM
  • But you can use Visual Studio 2010 for your Pipeline Components, even if your BizTalk Server is 2013.

    Morten la Cour

    Thursday, June 5, 2014 11:42 AM
  • The problem is I am not able to install the custom pipeline component wizard.
    Correct, unfortunately. But you can create the Pipeline Component project in VS 2010, then Add it to you VS2012 BizTalk Solution and it will work fine.
    Thursday, June 5, 2014 11:57 AM
  • I still don't understand why you can't just utilize all the data that are already there in the Tracking database? You could build a UI (web etc.) on top of the Tracking database  and show all the information you require that way, without having to build all this.

    Morten la Cour

    Thursday, June 5, 2014 12:13 PM
  • The point of the Pipeline Component is to set a Custom Property to Track on.  In this case, it would be just the file name.

    At least that's what I meant to recommend :).

    Thursday, June 5, 2014 1:51 PM
  • That's my point, the file name is already present in the Tracking Database

    dbo.dta_messageinoutevents column: strUrl

    Morten la Cour

    Thursday, June 5, 2014 2:00 PM
  • Also, I just has a flash, since the tracking query supports 'Like', you can just enable tracking on FILE.ReceivedFileName.

    Then query on ReceivedFileName Like youfilename.txt

    Thursday, June 5, 2014 6:08 PM
  • I was thinking that the custom pipeline component has to be used to get the messageId in a custom property at the receive port (not the message id that is passed to the orchestration) so that I can store it along with the file name and later use it for getting the message content from the tracking database.


    Regards, Vivin.

    Friday, June 6, 2014 4:54 AM
  • But everything is already there in the Tracking database, you just need to query it.

    Morten la Cour

    Friday, June 6, 2014 5:05 AM
  • Yes but in the orchestration the bts.messageid is returning the messageid of the message passed in the orchestration but I need the messageid at the receive port (message body is tracked) so that I can use it for getting content of the file at a later stage.

    Kindly advice which of the below methods is easier and recommended

    * Use SQL query [as the one provided above] in the orchestration to get the message id at the receive port.

    * Use a custom pipeline component to get the message id at the receive port in a custom property.


    Regards, Vivin.

    Friday, June 6, 2014 5:15 AM
  • Somewhere above, you said you need to query by file name.  If that is the case, try this:

    1. Enable Tracking on BTS.ReceivedFileName.  BizTalk.System -> Schemas -> FILE.bts_file_properties -> Tracking -> check FILE.ReceivedFileName

    2. Enable Property Tracking on the Receive Port.

    3. Run a Message

    4. In BT Admin, New Query, Tracked Message Events.

    5. Select the Schema of the Message that comes out of the Pipeline.

    6. FILE.ReceivedFileName should now appear on the list of searchable properties.

    7. Use LIKE with the file name.

    8. You will get the Pipeline Send Message

    9. Right click and choose Show Tracked Service Instance, then on the Pipeline Instance, choose Tracked Message Events.

    10. The Receive (unparsed interchange) is the incoming EDI.

    This is good to prove out using BizTalk Tracking, but I would still use a Custom Property rather than BTS.ReceivedFileName.

    Friday, June 6, 2014 11:55 AM
  • Hi Boatseller,

    The steps you have provided are really useful but what we are planning is to have our own interface for viewing the details and most importantly the end-user of the interface will not be having access to the BizTalk Server. So kindly advice how to proceed with getting the messageID.

    Thanks a lot for being patient and providing useful suggestions.


    Regards, Vivin.

    Friday, June 6, 2014 12:44 PM
  • Like I said, all the information is already there in the Tracking DB, so build your interface on top of that.

    Morten la Cour

    Friday, June 6, 2014 12:47 PM
  • Like I said, all the information is already there in the Tracking DB, so build your interface on top of that.

    +11  This demonstrates that all the data you need is alredy in BizTalk Tracking.  It's perfectly fine if you want to develop your own interface.  FYI, you can configure BT Admin to show only the Tracking Database.

    You should also give serious consideration to BizTalk 360.  It has much more granular access control then BT Admin and can also query the Tracking Database. 

    Here's a hint, you should follow the same pattern used by BT Admin:

    1. Query dta_MessageFieldValues with the file name
    2. Join/query dta_MessageInOutEvents to get the other uidMessageInstanceId (you mostly have this query already).
    3. Retrive the message content with the WMI MSBTS_MessageInstance class.

    • Marked as answer by Angie Xu Monday, June 9, 2014 5:20 AM
    Friday, June 6, 2014 1:58 PM