none
BizTalk 2009 - Store Subdocument as EDI in Database RRS feed

  • Question

  • I am working with a client using BizTalk Server 2009 Standard edition that processes 837 claims using the 4010 Multiple schema X12. The process uses the EDIReceive pipeline to import the data. Once the data is "split" into the XML message, they would like to store the "split" message as a field in the database, but they want to store the "split" message in the EDI format, not the XML format.
    Is it possible to convert the XML message back into EDI so that the EDI formatted message can be stored in the database?
    Also, I should mention that we are not using maps in the orchestration. We are using .Net to persist to the database, so we need to associate the EDI message with the persisted data before inserting into the database. Does anyone have any information they could share?
    Friday, November 13, 2009 5:48 PM

Answers

All replies

  • You are just going to need to use the EdiSend pipeline component to get the Xml back into Edi. Then you could use a port that writes to the database such as SQL or WCF-SQL. You could also use a pipeline component after EdiSend that writes the message to the database.

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Monday, November 16, 2009 12:24 AM
    Moderator
  • Hi,

    What do u exactly mean by "they want to store the "split" message in the EDI format, not the XML format."
    If you are asking how to convert XML message to EDI format the simple answer is to use EDISend pipeline or EDI Assembler component if you are using the custom component.
    Have you defined the database to store the message. You can also try to enable tracking which does store the message as IMG datatype under tracking databases.
    Elaborating more about your require will help you to buy a better answer.

    Regards
    Vishnu
    Vishnu
    Monday, November 16, 2009 1:20 PM
  • When the EDI file is received, it is processed using the EDIReceive pipeline against the X12 4010 Multiple Schema. After the pipeline converts the individual messages to XML (this is what I am calling "split" message), we need to convert the XML message back to the EDI format, associate the message with the Claim record that will be stored in the database using an internal primary key so that the original message can be viewed within an application. The database has been designed to store the EDI message as a column within the database.

    I am relatively new to BizTalk development, so I am not sure if I understand the use of the send pipeline. In this case is the send pipeline connected to a physical port where I need to send and then receive the information?
    Monday, November 16, 2009 3:09 PM
  • Hi,

    PLease follow the below steps.

    1- Create a send port and at the send port use the edisend pipeline. This pieline will convert the message back to edi.
    2- At the send port enable the tracking to track the message for before and after receive.
    3- In the tracking database,[table name trackingparts_01,02] the message body will be stored as img datatype, which could be viewed later.

    Does you customer also needs some kind of interface to view these messages or just storing the message body is sufficient.
    Hope this should be helpful.

    Regards
    Vishnu

    Vishnu
    Tuesday, November 17, 2009 8:34 AM
  • The customer will need to view the image within a custom application that is being built. Will I be able to "link" the information from the Tracking Database with the application database?
    Tuesday, November 17, 2009 3:01 PM
  • I recommend storing the message in a custom database rather than the tracking database because the tracking database gets periodically cleaned out.

    Thanks,
    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Tuesday, November 17, 2009 3:26 PM
    Moderator
  • Yes, you should be able to view the message. with the help of stored procedure you can retrive the message from tracking database.
    You can also have a look at the Microsoft.BizTalk.Operations.dll, possibly you may find some helpful methods.

    Regards
    Vishnu
    Vishnu
    Tuesday, November 17, 2009 4:24 PM
  • After reading some of the posts, I believe that storing the information in the tracking database is not the way to go...at least for a long term solution since the information is purged periodically. I will look into the Microsoft.BizTalk.Operations.dll based on the reponse above. Do you happen to know if there is an example of reading data from the tracking database?
    Tuesday, November 17, 2009 9:46 PM
  • Hi,

    You could possibly look into below sample which uses BAM API and Operations dll to get the message. I used SQL query and converted the img datatype to string fomat. That stored procedure is somewhere in my archive as it was long back.
    But this sample will help you a lot in regard with this.

    http://download.microsoft.com/download/b/1/d/b1d9ddf9-88c6-4d4e-abea-4787fdc85bec/bamhatcorrelation.exe

    Regards
    Vishnu
    Vishnu
    Wednesday, November 18, 2009 9:13 AM
  • Hi,

    1. Create a send port(file) and at the send port use the edisend pipeline. This pieline will convert the message back to edi. (defined in a post above)

    2. If custom db is SQL Server use SQLAdapter to write edi to db.

    For db without BizTalk adapter create custom pipeline component to write message to db or write custom client in language of choice for specific db to write message from file location.

    Thanks,

    William
    Wednesday, November 18, 2009 12:18 PM