none
How to insert records into multiple tables using the WS-Custom Oracle adapter RRS feed

  • Question

  • Hi All,

    I am fairly new to the Adapter world. So please forgive me if I am asking something stupid. I have a situation where I need to load the data from my source schema (EDI 837P Claim) to multiple tables in Oracle. I was able to load data to a single table with the help of guys from this forum. Now I need to load data to mutiple tables. Using the Consume Adapter Service Add-in I selected the tables I need and the action which is insert in my case. But the add-in created seperate schemas for each table. I need to insert the EDI 837P data to 5 tables in Oracle. I don't know how to deal with 5 different schemas. I thought the add-in would create a single schema and it would be just a matter of mapping my source schema to the destination schema. Could you guys please guide me in the right direction?

    thanks
    Monday, April 6, 2009 9:41 PM

Answers

  • Hi Joseph,

    From the question, I'm presuming that you are using BizTalk Adapter Pack v1 (BAPv1) Oracle database adapter. In this version of the adapter, there is no provision to insert the data into multiple tables by providing a single input xml file, since each input xml for insert operation should carry action, which contains the name of the table. You need to call the insert on each table separately providing data to be inserted. For this, you need to map the source schema into 5 different destination schemas, one for each table insert operation, and then call insert separately for each table.

    In the next version of BAP called BAPv2 (which is about to RTM), we have added this functionality to execute multiple operations by providing a single input xml. This functionality is called 'Composite Operation', which lets you provide a single xml containing different operations on different tables (or calling different SPs). In this case also, you need to map the source schema into 5 different tables schemas. Then write a single input xml which carries 5 insert operations calls.

    Hope you find this information useful.

    Thanks
    Gaurav


    • Marked as answer by G.Joseph Tuesday, April 7, 2009 7:53 PM
    Tuesday, April 7, 2009 4:47 AM
  • Hi gjoseph,

    Are you using BizTalk adapter pack 2.0? If yes, you can make use of the Composite Operation feature to insert into multiple tables using one single message. The help file that ships with the adapter has a very nice step by step tutorial just for this. You will be able to combine the 5 schemas you have into one composite schema.


    In case you're using adapter pack 1.0, you will need to create 5 different kinds of messages, and do one of he following:

    1. Create 5 physical send ports, each configured for a different table
    2. Create 1 physical send port, and use BTS Action mapping (described in the documentation)
    3. Use a dynamic send port (described in the documentation)

    Approaches 2 and 3 are good ones, and approach 2 will be easier to work with.

    I hope this helps,

    Thanks,
    Manas
    • Marked as answer by G.Joseph Tuesday, April 7, 2009 7:53 PM
    Tuesday, April 7, 2009 4:47 AM

All replies

  • Hi Joseph,

    From the question, I'm presuming that you are using BizTalk Adapter Pack v1 (BAPv1) Oracle database adapter. In this version of the adapter, there is no provision to insert the data into multiple tables by providing a single input xml file, since each input xml for insert operation should carry action, which contains the name of the table. You need to call the insert on each table separately providing data to be inserted. For this, you need to map the source schema into 5 different destination schemas, one for each table insert operation, and then call insert separately for each table.

    In the next version of BAP called BAPv2 (which is about to RTM), we have added this functionality to execute multiple operations by providing a single input xml. This functionality is called 'Composite Operation', which lets you provide a single xml containing different operations on different tables (or calling different SPs). In this case also, you need to map the source schema into 5 different tables schemas. Then write a single input xml which carries 5 insert operations calls.

    Hope you find this information useful.

    Thanks
    Gaurav


    • Marked as answer by G.Joseph Tuesday, April 7, 2009 7:53 PM
    Tuesday, April 7, 2009 4:47 AM
  • Hi gjoseph,

    Are you using BizTalk adapter pack 2.0? If yes, you can make use of the Composite Operation feature to insert into multiple tables using one single message. The help file that ships with the adapter has a very nice step by step tutorial just for this. You will be able to combine the 5 schemas you have into one composite schema.


    In case you're using adapter pack 1.0, you will need to create 5 different kinds of messages, and do one of he following:

    1. Create 5 physical send ports, each configured for a different table
    2. Create 1 physical send port, and use BTS Action mapping (described in the documentation)
    3. Use a dynamic send port (described in the documentation)

    Approaches 2 and 3 are good ones, and approach 2 will be easier to work with.

    I hope this helps,

    Thanks,
    Manas
    • Marked as answer by G.Joseph Tuesday, April 7, 2009 7:53 PM
    Tuesday, April 7, 2009 4:47 AM
  • Gaurav and Manas,

    Thanks for the valuable information. To tell you the truth I don't even know which one I am using. When I searched for Oracle Adapter, I found couple of different items. Biztalk Adapter Pack, MS BizTalk Adapters for Enterprise Applications, WCF Oracle dapter, WCF LOB Adapter SDK etc. So I wasn't sure when one to install. Under program files I do see two folders. Microsoft BizTalk Adapter Pack and Microsoft BizTalk Adapters for Enterprise Applications. The doucmentation under Microsoft BizTalk Adpater Pack indciates BAPv1. This was installed a while back. I recently installed the BizTalk Adapters for Enterprise Applications and WCF LOB Adapter pack SDK. When you choose WCF-Custom from the Adapter drop down and configure the bidings to use oracleDBBinding, which one it uses? I read on the net about the WCF ORacle adapter that uses the ODP.net as opposed to ODBC. So I searched for WCF Oracle Adapter and all I found was the WCF LOB ADapter SDK. I would like to use the one that uses the ODP.net driver instead of the ODBC driver. So could you please tell me which one should I be using? I am kind of lost with all these adapters. Thanks for your help.

    thanks

    Tuesday, April 7, 2009 2:22 PM
  • Hi,

    The WCF based biztalk adapter pack is the one that uses ODP.NET. The BizTalk Adapters for Enterprise Applications are the older adapters, and the oracle adapter in it uses ODBC.

    When you choose oracleDbBinding from the dropdown, it will use the WCF based adapter (i.e. the BizTalk adapter Pack adapter)

    There are two versions of the WCF based biztalk adapter pack (BAP) - V1 which you seem to be using, and V2 which is in Beta right now, and available through Microsoft connect website.

    So in all there are 3 possibilities:

    a. Oracle Db adapter that comes with Biztalk adapters for enterprise apps (deprecated in BizTalk 2009)
    b. Oracle Db adapter 3.0 in Biztalk adapter Pack V1
    c. Oracle Db adapter in Biztalk adapter Pack V2 (currently in Beta)

    Based on what you mention about the doc, you seem to be using (b.)

    Hope this helps!
    Tuesday, April 7, 2009 2:47 PM
  • Manas,

    Thanks a ton for the detailed explanation. Things make sense now :) The approch for BAPv1 seems a little complicated. I am a bit concerned about performance since I have to deal with multiple messages and multiple calls to the adapter. Will there be a performance gain if I go with the "Composite Opeartion" feature in BAPv2?

    thanks
    Tuesday, April 7, 2009 3:34 PM
  • There will be a some performance gain, as well as the solution will be easier to develop using the Composite Operation approach. Another advantage of Composite Operations is that it allows all the operations to be invoked in a single transaction.

    Composite Operations are preferred for the ease of usage and transaction support rather than the potential performance improvement.
    Tuesday, April 7, 2009 4:04 PM
  • Thank you. I will check out BAPv2.
    Tuesday, April 7, 2009 7:53 PM
  • I know this thread is a couple of weeks old, but I just read it.  I'm not sure why you need to use Biztalk to do the 5 inserts.  Why not send the data once to oracle using the WCF adapter and then use Oracle to distribute the data to whereever it needs to go.  I have a single send port to send data to Oracle.  The data is inserted into a single table.  I then use an Oracle scheduled job (via the DBMS_JOB package) to process this data to their final destinations.  Each record in this table has a record type which identifies what the data is.  A stored procedure and triggers contain the logic on how the data is handled.  It may not be perfect for all circumstances, but it does have some advantages, which I list below:

    1)  Reduced bandwidth.  Instead of sending the same data 5 times to the Oracle database, you are sending it once. 
    2)  Modularity.  All processes that need to send data to Oracle can do so without reinventing the wheel.  If your needs change and you have to load the data into 7 tables, then you don't need to change anything on the Biztalk side.  Of course there may be a more fundamental question of why you need 5 copies of the same data in your database. 
    3)  Maintainability.  The fewer interconnections between disparate systems, the easier it is to upgrade one without effecting the other. 

    In short, let Biztalk do what it does best, which is to get data from one place to another. Don't use Biztalk to do what is best done in Oracle via table triggers and stored procedures.

    Wednesday, April 22, 2009 3:26 PM
  • Mathgeekjoe,

    Thank you for your input. Currently we use that approch in production. When we started EDI processing there were only three tables invloved on the ORacle side to process claims. A Batch header table that stores the key Interchnage information like SenderID, ReceiverID, Control Number etc, an IN table that sores the xml representation of the claim generated by BizTalk along with some key values and an OUT table that stores the transformed data in positional format in a CLOB field along with some key fields (Mainframe is the ultimate destination). The Business rules were implemented in the maps. Things were pretty straightforward. We had oracle jobs to write the claim data from the CLOB field to files that gets FTPd to the mainframe.

    Later on we had to load the transformed claims data from the CLOB fields to normalized tables inorder to perfrom certain edits on the claims. We also moved the Business rules from BizTalk to Oracle. So we setup a trigger on the OUT table to load the data to the staging tables. That's all working fine and definitely we have the adv you mentioned in your post. The downside to this approch is that everytime we make a change to the cobol copybook, we have to make the corresponding changes to the positional BizTalk outbound doc spec, Oracle trigger and so on. Things get more complex when new fields are added to the middle of the copybook and so. So we are trying to get rid off the positional stuff. Things will be easier with the multi insert approch. True, the ORacle client will have to make 5 inserts. We want to see how the performance will be with this approch. Depending on that we will decide whether to keep the current approch or not.

    thanks
    Thursday, April 30, 2009 11:55 PM
  • Hi Mathgeekjoe,

    I saw that you mentioned Cobol Copybook in this post. You might be wondering why am i writing to you. The pupose is that, i am newbie in using Biztalk and just starting to learn Biztalk. I too have a scenario in my Biztalk Application with respect to copybook layouts. here is what i am doing.

    I am working in health care Biztalk application which is aiming at processing inbound EDI & proprietary file formats(copybook definition files) and pass the debatched files to the .NET Application. I have to configure Biztalk application to pick up these files in different file formats. The count of this diferent file formats is nearly equal to 180. If it would have been a single file, i would have mapped them using the file conversion wizard in Biztalk. 180 file formats is scarring me a lot. I would want to automate this process.

    Can you all help in guiding me to choose a best approach to finish this application?

    1) Is there a way that we can come up with an small .NET application where we can convert the copybook definitions into XML Schemas(.xsd file)?
    2) If not this what is the other way of doing this?

    Thanks.
    Sravan Kasyap
    Tuesday, June 16, 2009 5:45 PM