none
Convert 834 Xml into SQL tables using WCF_SQL Adapter RRS feed

  • Question

  • I am able to Convert the 834 EDI 4010A1 document into a XML in Biztalk 2009 using the EDIRecieve pipeline component but don't know how to use the WCF-SQL adapter to convert this XML into SQL tables. I have tried couple of WCF_SQL adapter tutorials but all ofthem work with just a straight schema which have no more than 3, 4 elements but not with an X12 document.
    1. Does anyone have a sample of how to use this WCF-SQL adapter to load a complicated XMl into SQL? if possible an xml of 834 ?
    2. Does anyone have a sample Orchestration for an 834 Process ?
    3. Is there any other better way of directly sending data into SQL tables from a 834 document ?
     
    • Edited by rkmamidi Tuesday, February 16, 2010 5:49 PM
    Tuesday, February 16, 2010 3:54 PM

Answers

All replies

  • 1. No, the WCF-SQL adapter cannot log directly based on x12 documents. You would need to map the data to a procedure for saving it. If you want data in individual columns like for reporting purposes, this would be the way to go. This article (for SQL 2008 usage) can make it easy to pass along multiple tables as parameters which would be useful for a complex EDI message: http://blogs.msdn.com/biztalkcpr/archive/2009/10/05/inserting-parent-child-records-with-identity-column-using-wcf-sql-adapter-in-one-transaction.aspx
    2. Not sure of where to get a sample for this.
    3. Traditionally, some people log the data in a custom pipeline component. If you just want the blob of the data this is a simple way to go.

    Thanks, 
    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Tuesday, February 16, 2010 4:47 PM
    Moderator
  • I have created an adapter for this very issue. I have written about it here:


    Eric Stott [http://blog.biztalk-info.com] - Mark as Answer if this reply does.
    Tuesday, February 16, 2010 11:07 PM
  • Thanks for your reply Eric. I am stuck at a point in your tutorial How do you get the SQLBULKXML adapter. Do we have to buy your product to get that adapter.

    Rupesh
    Wednesday, February 17, 2010 9:22 PM
  • I am not looking for a blob but i have a 834 table structure and i want to load the the data coming in the 834 X12 file into those tables.

    Rupesh
    Wednesday, February 17, 2010 9:24 PM
  • Yes, I had to purchase two tickets to get it to work with BizTalk.
    I found with the SQL Adapter, that there were three things that prevented me from using it in large EDI solutions: 1. The SQL adapter converts data to a string (which causes out of memory issues in certain cases) and 2. There is a 10,000 variable limitation (SQL Limitation) and 3. It was terribly slow as the SQL adapter does one row inserts.
    With EDI, you are looking at 10,000 row inserts per transaction. 

    Eric Stott [http://blog.biztalk-info.com] - Mark as Answer if this reply does.
    Thursday, February 18, 2010 7:12 AM
  • 1. No, the WCF-SQL adapter cannot log directly based on x12 documents. You would need to map the data to a procedure for saving it. If you want data in individual columns like for reporting purposes, this would be the way to go. This article (for SQL 2008 usage) can make it easy to pass along multiple tables as parameters which would be useful for a complex EDI message: http://blogs.msdn.com/biztalkcpr/archive/2009/10/05/inserting-parent-child-records-with-identity-column-using-wcf-sql-adapter-in-one-transaction.aspx
    2. Not sure of where to get a sample for this.
    3. Traditionally, some people log the data in a custom pipeline component. If you just want the blob of the data this is a simple way to go.

    Thanks, 
    If this answers your question, please use the "Answer" button to say so | Ben Cline

    Thanks for your analysis! It helps me out of the problem.
    Sunday, September 26, 2010 2:42 AM
  • Finally we used this method to Load 834 X12 Files into SQL Server.

    1. Created a Orchestration which converts the X12 into XML using EDI Receivce pipeline.

    2. Calls an external assembly which loads the XML into LINQ tables using the original 834 5010 schema.

    3. Then we created approx 25 small X12 schema from the big 834 Schema (1 for every segment)

    4. Using the a SSIS pkg and Small schemas loaded the LINQ tables into Sql server.

    Was able to Push the 15,000 member records (Approx 15MB X12 file = 250 MB XML into database from start to finish in less than 10 Min )

    Thursday, October 7, 2010 11:43 PM