none
How to put 835's into SQL using Biztalk? Or something other way? RRS feed

  • Question

  • As the title states I am wondering what is a good approach for doing this?  I would have an 835 edi file disassembled to xml in Biztalk. I did some previous splitting of the file using maps. 

    Next, I would want to do three things:

    1. Put the xml'ed 835 file into SQL server. Note I would want to put all data into many tables and fields. So, I am not putting one field with the whole 835 file. 

    2. And somehow refer that the file is from a certain payer.  I'm not sure how to handle the fact that the payer information was in the ISA (about 30 different payers), because it was stripped out of the message into context properties.

    3. Somehow I need to be able to say that this data is all related to the original file....

    Any Ideas as to what may be a good solution. After that is done I will be able to give the accounting department views into the data, run reports and other stuff.

    I read some things about sql adapter and datagrams and just don't see it...how this would work with such a looping structure. 

    I am Confused how to approch this..Biztalk with sql adapter - .net component in an orchestration - SSIS - ?????


    vb.net programmer
    Friday, July 8, 2011 4:21 PM

Answers

  • Hi,

    For SSIS approach for EDI, see this thread.

    HTH

    Regards,

    Steef-Jan Wiggers
    MVP & MCTS BizTalk Server 2010
    http://soa-thoughts.blogspot.com/
    If this answers your question please mark it accordingly


    BizTalk
    Saturday, July 9, 2011 10:05 AM
    Moderator
  • Hi,

    Doing this is in BizTalk is not at all dauting. You need to create an SP which reads the XML message or an string containing xml message (will not suggest the section since string has a limitation in terms of amount of data which you pass) and updates the tables. Any data present in ISA and you need this to be part of xml message for SQL update, you require to add the elements in your update xml message, promote these and enrich these fields inside your orchestration. If you are looking out for some code help, please let me know.  

     


    Best Regards, Vishnu
    Sunday, July 10, 2011 6:05 AM

All replies

  • Hi,

    For SSIS approach for EDI, see this thread.

    HTH

    Regards,

    Steef-Jan Wiggers
    MVP & MCTS BizTalk Server 2010
    http://soa-thoughts.blogspot.com/
    If this answers your question please mark it accordingly


    BizTalk
    Saturday, July 9, 2011 10:05 AM
    Moderator
  • Hi,

    Doing this is in BizTalk is not at all dauting. You need to create an SP which reads the XML message or an string containing xml message (will not suggest the section since string has a limitation in terms of amount of data which you pass) and updates the tables. Any data present in ISA and you need this to be part of xml message for SQL update, you require to add the elements in your update xml message, promote these and enrich these fields inside your orchestration. If you are looking out for some code help, please let me know.  

     


    Best Regards, Vishnu
    Sunday, July 10, 2011 6:05 AM