none
WCF custom port Error on inserting data into SQL table RRS feed

  • General discussion

  • Hi all,

    I'm using the WCF Custom Adapter to run an RFC in SAP and get data from that system, I need to insert it into my SQL Server table. Everything seems to be working fine but at the point when I'm actually about to get my data inserted, I get this error:

    Details:"Microsoft.ServiceModel.Channels.Common.XmlReaderParsingException: During an INSERT, UPDATE or DELETE operation, for some row in the input message, some columns were found more than once.

    the thing is the data in SAP was not properly structured or maintained, and there are a lot of duplicate rows, so I can understand why I would get this error. I did not set any primary keys and all columns allow nulls in the table (I thought that would resolve the issue) but it seems the error comes from bizTalk before inserting. Is there any way to avoid getting this error?

     

    Thanks

    Saturday, July 16, 2011 1:37 PM

All replies

  • the data in SAP was not properly structured or maintained, and there are a lot of duplicate rows, so I can understand why I would get this error. I did not set any primary keys and all columns allow nulls in the table (I thought that would resolve the issue) but it seems the error comes from bizTalk before inserting. Is there any way to avoid getting this error?

    Hi Samual,

    You could try to get every single row from your original message from SAP and try insert each into SQL Server. You could apply concept of many to one mapping; for reference see this post. You will be left with duplicate rows still and I assume you do not want them to inserted in SQL Server, so you will have to apply some logic to get rid of them.

    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
    Sunday, July 17, 2011 1:32 PM
    Moderator
  • Hi Steef,

    Actually, I do want to insert the duplicate rows at this stage. The guys on the SAP team actually don't know about all the duplicats in their data... so I would use the duplicates I get inserted into SQL server to create reports showing this to management...which will be used to resolve the problem at the source...then we don't get duplicates

    but is there any way I can disable the check from throwing this error during xml parsing?

    Also, extracting 1 record at a time would take forever since this our first time to run and there are 100,000 records...we need to do it in batches of 1000-5000 at a time

    Thanks

    Sunday, July 17, 2011 3:23 PM
  • Hi Samual,

    An alternative would be using SSIS, see this post for reference or poll data out of SAP and debatch it in a pipeline and then insert into SQL Server.

    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
    Monday, July 18, 2011 10:01 AM
    Moderator
  • What is the primary key on your SQL table?  I suspect its something that is duplicated in your data.  Add a new identity column and make that your primary key. 
    Down there somewhere, its just ones and zeros.
    Monday, July 18, 2011 6:08 PM
  • El Bo,

    no primary key defined for the table..which is why I thought it would not cause an error...but it seems the error is during the XML parsing..

     

    Steef,

    At this stage, I can't really re-develop. this orchestration works fine, extracts everything...I just need to resolve it checking for duplicate records

    Monday, July 18, 2011 8:31 PM
  • Anyone? I need help this was supposed to be live last week...

     

    there HAS to be a way to stop the xml validation on duplicate records!

    Thursday, July 21, 2011 5:22 PM
  • Hi Samual,

    I think you do need to insert each record (row) one by one and that will take some time. For table you want to insert records into you could use identity insert, see MSDN Working with BizTalk for SQL Server binding properties.

    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
    Tuesday, July 26, 2011 9:16 AM
    Moderator
  • Thanks Steef. Could you elaborate a little more? Where exactly do I need to make the change? on the port? in the map? schema?
    Saturday, July 30, 2011 6:30 AM
  • If Stef is suggesting what I think he is suggesting, you need to do a few things:

     

    1. Create a custom pipeline to receive your messages from SAP

    2. Create a schema for the inbound messages that will make them split in to individual messages (de-batch).  There is a good blog post by Richard Seroter on that here: http://seroter.wordpress.com/2010/04/08/debatching-inbound-messages-from-biztalk-wcf-sql-adapter/

    3. Put the schema in the dissasemble stage of the custom pipeline. 

    4. Now the individual messages are hitting the message box

    5. Create a stored proc to insert one record at a time

    6. Use the Add Generated Item to create a schema for use with the WCF-Custom adapter

    7.That will give you a schema and a binding file.  Open the binding file, change the send port to something more friendly.  When you apply the binding file, go in the send port and set a filter on receiveportname to the receive port the SAP messages came in on

    8. Create a map from your SAP schema to the schema that was generated in step 6

    9. Apply that map on the send port 

    10.  At this point you probably want to export the bindings to capture your changes (map, port name, filter, etc)

    Now if you want to get rid of the duplicates, that's a SQL stored proc.  Let me know if you need some guidance on how to write that. 

     

     


    Down there somewhere, its just ones and zeros.
    Saturday, July 30, 2011 4:40 PM
  • Hi El-Bo,

    That is a way to do it, but it will with numerous records to be inserted create overhead in roundtrips to messagebox (I/O). I like process you decribed and if you keep if solely message based using port mapping e.a.

    Cheers,

    Steef-Jan Wiggers
    MVP & MCTS BizTalk Server 2010
    http://soa-thoughts.blogspot.com/

     


    BizTalk
    Saturday, July 30, 2011 7:26 PM
    Moderator
  • Hi El Bo and Thanks,

     

    ok once I get the messages debatched in the piepline I'm already in the middle of the orchestration, will the debatched results all end up in the same instance? Is there any way to loop through them because I already had a map done which mapped the output from SAP to the table I am trying to insert into

     

     

    Appreciate the help thanks

    Thursday, August 4, 2011 8:12 PM
  • Hi Samual,

     

    You debatch in the pipeline and they publish to the message box as individual records.  So the subscribing orchestration receives individual messages, one orchestration instance per message.  No need to loop. 

     

    Now remember, this means your pipeline is just publishing to the message box, and your orchestration is direct bound and subscribing (probably using the receive port name).  You don't tie the orchestration to the receive port that has the pipeline that does the debatching. 

     

    That is why it runs so much faster, when you loop in an orchestration you keep making serialization runs, and it gets bigger and bigger each loop, which means your SQL server log gets REALLY big.  Double the number of records, and the SQL log grows more than double in size, so as the number of records goes up that gets progressively worse.  

    By debatching in the pipeline and having one or more orchestrations subscribe to the message, you perform many small units of work rather than one big one.  

    In general, I find BizTalk runs faster and more stable when you give it many small units of work.  I have a system that debatches 80,000 records and spins up 160,000 orchestrations to run them against the rule engine, and it is much faster to do it this way than to have a loop that runs through the records.  We even tried it with looping through only a thousand records at a time (so there were only 16 orchestration instances), and even that was slower than just one record per orchestration. 

    Hope that helps, but if you need more guidance just ask! 

     

    El Bo 

     

     

     

     


    Down there somewhere, its just ones and zeros.
    Thursday, August 4, 2011 9:06 PM
  • I was wondering if that was how it was gonna work. so if I know will have multiple instances of my orch do I need to seperate into a new orchestration?

    the current SAP port is a 2 way port and the response would now be debatched with the new pipeline. so will it work fine? or do I need to drop those files into some folder and have a new orchestration pick them up and process them one by one?

    Friday, August 5, 2011 1:41 AM
  • Process them one by one, but there is no need to drop them in to a folder.  That's what the messagebox is for - publish to the messagebox, and have the other orchestration subscribe. 
    Down there somewhere, its just ones and zeros.
    Saturday, August 20, 2011 7:48 PM