Bulk Insert using “CompositeOperation” in WCF-SQL BizTalk Adapter

This is amazing, now we don’t need to de-batch the incoming messages to insert into database tables. The single multi-record message send to the send port can be inserted into database using “CompositeOperation” in WCF-SQL adapter soap action header tab. This is know as Single file method.

Create schema with the below structure. Transaction node max occur is set to “unbounded”. This is the incoming message.

image

Create table “Transactions” as per above structure on SQL Server 2008.

image

Create “insertTransaction” store procedure.

image

To add the SQL Server schemas used by the WCF-SQL Adapter from the BizTalk solution you can right click on the BizTalk Project, select Add, and then ‘Add Generated items’. Now you can use consume adapter service option.

We will get the consumer service wizard where you can configure and add the database schema. Add your sql server details in the configuration box and click connect. You will see the below screen, click on Store Procedure and add the newly created store procedure “insertTransaction” and click ok.

image

This will generate schema for the store procedure as per below.

image

Please note that the insertTransactions node is the root node, and therefore can only exist once in the XML instances for this schema. What we used to do before is de-batch the incoming messages using the pipeline component and map to the single node on the send port and send to the database to the database to insert. Now here composite operations come in handy, the composite operations in the adapter have been described on Richard Seroter’s book (free sample chapter on the WCF-SQL Adapter) and Brian Loesgen’s blog.

Now, I modified the schema as per below and made the “insertTransactions” node set max occur to “unbounded”.

image

This allows me to map from the non de-batched message to the schema created above.

image

I also created orchestration to transform the incoming message using the above Map, although, this is optional, you can use the map on the send port.

image

Compile and deploy the solution. After deploying the solution I created receive port/locations to receive the incoming message and WCF-SQL Send port as per below, with the Soap Action Header as “CompositeOperation”. Yes, that is the key for bulk insert.

image

Now orchestration is bounded to the receive location and send port. Drop xml file in the receive location and yes all the records flowed through to the database table.

When we map the entire message to the composite schema we end up with one transaction that wraps around all the store procedure calls. The best thing is , If we monitor the Transactions/sec for the database we see barely any activity when we use the single file method as compare to de-batch method.

http://www.sharptalktech.com

Thanks.

Regards

Advertisements

14 thoughts on “Bulk Insert using “CompositeOperation” in WCF-SQL BizTalk Adapter

    • Hi Mark,
      I found this very handy and easy to use, we do not need to de-batch message and do not spun thousands of orchestrations. For Retail I believe it shall be only 1 day work to include Single File Method in the solution.
      Regards
      Shadab

  1. Nice article. I have used it. One question- Can you also on the enhancement steps. For ex, if a new column has added to
    the table and SP changed. Do we go go Schema versioning ? Do we have to regenrate schemas or just add the new field to the existing
    schema with a new version? Appreciate if you can give a detailed steps.

    • Hi Balaji, Thank you for the comment. Well, Schema versioning in BizTalk is always a tough thing to do. If you do small change in the schema for the new solution and the schema is already there in the production you need to be very careful. However, you can add the field in the schema, it shall work, you do not need to re-generate schema from the store procedure, but I’m not sure what happens if we use the .strongly-typed Procedure, we might need to change the fields in the data-array in this case.
      Regards
      Shadab

  2. Hi,
    its a nice article.i tried with compositeoperation but its not working.
    Soap Action Header as “CompositeOperation”
    please guide me

      • HI Shadabanwer,
        I am using “OracleDBBinding”..in my project,input schema is Codeco 95B EDI ..and I have to save the output to Oracle DB.Destination schema is Stored Procedure called by using WCF Oracle DB binding.
        Sample INPUT:
        UNA:+.? ‘
        UNB+UNOA:2+UAILKUTC+MSC+130404:0000+3851′
        UNH+1+CODECO:D:95B:UN:SMDG10′
        BGM+36+MSCU6614580+9′
        RFF+AAA:1′
        TDT+20+DF312R+1++MSC:172:20+++A8UH5:103::ESTEBROKER’
        LOC+11+UAILK:139:6′
        DTM+132:201303250600:203′
        DTM+133:201303252000:203′
        NAD+CA+MSC:160:20′
        NAD+MS+UAILKUTC:160:20′
        GID+1′
        EQD+CN+MSCU1071788+2200:102:5++3+5′
        RFF+BN:DUMMY’
        RFF+BM:MSCUQP850277′
        DTM+181:201304032122:203′
        LOC+9+GRPIR:139:6′
        LOC+8+UAILK:139:6′
        MEA+AAE+G+KGM:20650′
        MEA+AAE+AAL+KGM:18200′
        MEA+AAE+T+KGM:2280′
        SEL+FEX3765835+CA’
        TDT+1++3+++++BH9001EI:146′
        LOC+165+UAILK:139:6+UAILKUTC:TER:ZZZ’
        EQD+CN+MSCU1071789+2200:102:5++3+5′
        RFF+BN:DUMMY’
        RFF+BM:MSCUQP850277′
        DTM+181:201304032122:203′
        LOC+9+GRPIR:139:6′
        LOC+8+UAILK:139:6′
        MEA+AAE+G+KGM:20650′
        MEA+AAE+AAL+KGM:18200′
        MEA+AAE+T+KGM:2280′
        SEL+FEX3765835+CA’
        TDT+1++3+++++BH9001EI:146′
        LOC+165+UAILK:139:6+UAILKDEP:DEP:ZZZ’
        EQD+CN+MSCU1071790+2200:102:5++3+5′
        RFF+BN:DUMMY’
        RFF+BM:MSCUQP850277′
        DTM+181:201304032122:203′
        LOC+9+GRPIR:139:6′
        LOC+8+UAILK:139:6′
        MEA+AAE+G+KGM:20650′
        MEA+AAE+AAL+KGM:18200′
        MEA+AAE+T+KGM:2280′
        SEL+FEX3765835+CA’
        TDT+1++3+++++BH9001EI:146′
        LOC+165+UAILK:139:6+UAILKDEP:DEP:ZZZ’
        CNT+1:1′
        UNT+36+1′
        UNZ+1+3851′
        for each EQD segment looping should be called and each insert should be there in oracle table.
        Generated destination schema:

        I am unable to loop on node.as per your article details
        finally I added two child records as “Request” and “Response” then now my final destination schema looks like,

        I am getting the below error: Please guide me .
        The adapter failed to transmit message going to send port “WcfSendPort_OracleDBBinding_BIZTALK_TEST_Procedure_Custom” with URL “oracledb://ibox11g/”. It will be retransmitted after the retry interval specified for this Send Port. Details:”Microsoft.ServiceModel.Channels.Common.XmlReaderParsingException: Unexpected start node “Request” with namespace “http://Microsoft.LobServices.OracleDB/2007/03/BIZTALK_TEST/Procedure” found.

        Procedure:
        create or replace
        procedure ibp_edi_cont_event_insert
        (p_event_container in ibt_edi_container_events.event_container%type,
        p_event_date in Varchar2,
        p_event_code in ibt_edi_container_events.event_code%type,
        p_event_cont_type in ibt_edi_container_events.event_container_type%type,
        p_event_iso_code in ibt_edi_container_events.event_iso_code%type,
        p_event_pay_load in ibt_edi_container_events.event_pay_load%type,
        p_event_trans_mode in ibt_edi_container_events.event_transport_mode%type,
        p_event_status in ibt_edi_container_events.event_status%type,
        p_event_current_loc in ibt_edi_container_events.event_current_location%type,
        p_event_curr_loc_name in ibt_edi_container_events.event_current_location_name%type,
        p_event_voyage in ibt_edi_container_events.event_voyage%type,
        p_event_bl in ibt_edi_container_events.event_bl%type,
        p_event_release in ibt_edi_container_events.event_release%type,
        p_event_pre_booking in ibt_edi_container_events.event_pre_booking%type,
        p_event_transporter in ibt_edi_container_events.event_transporter%type,
        p_event_entry_type in ibt_edi_container_events.event_entry_type%type,
        p_event_depot in ibt_edi_container_events.event_depot%type,
        p_event_terminal in ibt_edi_container_events.event_terminal%type,
        p_event_org_sub_loc in ibt_edi_container_events.event_org_sub_location%type,
        p_event_dest_sub_loc in ibt_edi_container_events.event_dest_sub_location%type,
        p_event_barge_voyage in ibt_edi_container_events.event_barge_voyage%type,
        p_event_barge_name in ibt_edi_container_events.event_barge_name%type,
        p_event_return_depot in ibt_edi_container_events.event_return_depot%type,
        p_event_return_location in ibt_edi_container_events.event_return_location%type,
        p_event_return_loc_name in ibt_edi_container_events.event_return_location_name%type,
        p_event_return_terminal in ibt_edi_container_events.event_return_terminal%type,
        p_event_dest_location in ibt_edi_container_events.event_dest_location%type,
        p_event_dest_loc_name in ibt_edi_container_events.event_dest_location_name%type,
        p_event_scac_code in ibt_edi_container_events.event_scac_code%type,
        p_event_days in ibt_edi_container_events.event_days%type,
        p_event_customer in ibt_edi_container_events.event_customer%type,
        p_event_bic_code in ibt_edi_container_events.event_bic_code%type,
        p_event_damage_status in ibt_edi_container_events.event_damage_status%type,
        p_event_damage_reason in ibt_edi_container_events.event_damage_reason%type,
        p_event_damage_remarks in ibt_edi_container_events.event_damage_remarks%type,
        p_event_program in ibt_edi_container_events.event_program%type,
        p_event_remarks in ibt_edi_container_events.event_remarks%type,
        p_event_filter_1 in ibt_edi_container_events.event_filter_1%type,
        p_event_filter_2 in ibt_edi_container_events.event_filter_2%type,
        p_event_filter_3 in ibt_edi_container_events.event_filter_3%type,
        p_event_filter_4 in ibt_edi_container_events.event_filter_4%type,
        p_event_filter_5 in ibt_edi_container_events.event_filter_5%type
        ) as
        p_event_pid number:=0;

        begin
        p_event_pid:=ibt_edi_container_events_seq.nextval;

        insert into ibt_edi_container_events
        (event_pid,event_container,event_date,event_code,event_container_type,
        event_iso_code,event_pay_load,event_transport_mode,event_status,
        event_current_location,event_current_location_name,event_voyage,
        event_bl,event_release,event_pre_booking,event_transporter,event_entry_type,
        event_depot,event_terminal,event_org_sub_location,event_dest_sub_location,
        event_barge_voyage,event_barge_name,event_return_depot,event_return_location,
        event_return_location_name,event_return_terminal,event_dest_location,
        event_dest_location_name,event_scac_code,event_days,event_customer,
        event_bic_code,event_damage_status,event_damage_reason,event_damage_remarks,
        event_program,event_remarks,event_created_on,
        event_purge_year,event_filter_1,event_filter_2,event_filter_3,
        event_filter_4,event_filter_5)

        values (p_event_pid,p_event_container,to_date(p_event_date,’YYYYMMDDHH24MI’),p_event_code,p_event_cont_type,
        p_event_iso_code,p_event_pay_load,p_event_trans_mode,p_event_status,
        p_event_current_loc,p_event_curr_loc_name,p_event_voyage,
        p_event_bl,p_event_release,p_event_pre_booking,p_event_transporter,p_event_entry_type,
        p_event_depot,p_event_terminal,p_event_org_sub_loc,p_event_dest_sub_loc,
        p_event_barge_voyage,p_event_barge_name,p_event_return_depot,p_event_return_location,
        p_event_return_loc_name,p_event_return_terminal,p_event_dest_location,
        p_event_dest_loc_name,p_event_scac_code,p_event_days,p_event_customer,
        p_event_bic_code,p_event_damage_status,p_event_damage_reason,p_event_damage_remarks,
        p_event_program,p_event_remarks,sysdate,
        to_char(sysdate,’YYYY’),p_event_filter_1,p_event_filter_2,p_event_filter_3,
        p_event_filter_4,p_event_filter_5
        );

        commit;
        exception
        when others
        then
        dbms_output.put_line(‘Error in ibp_edi_cont_event_insert: ‘||sqlerrm);
        end;

        Send Port:
        Operation Name:IBP_EDI_CONT_EVENT_INSERT

        correct me if I am wrong.i am totally new to BizTalk.Please guide me Shadabanwer.

  3. Question: I have working WCF-SQL sample on my local box.
    Now, Suppose my stored procedure name got changed. And i/p , o/p parameters are the same for my stored procedure.

    Then What are the changes that i need to incorporate to make it work?

    I think i have to change record names such a way that they should match my stored proc name.
    i.e If sp name is “OrdersAbc” then i should change record names to ” OrderAbc”, “OrderAbcResponse”
    Please suggest.

    • Hi Vaibu,
      When you do add generate item for the store procedure, it will generate the correct schemas. Please go through the above link step by step. Let me know how you go and need any help.
      Regards
      Shadab

  4. Pingback: Issue with WCF-SQL Adapter with 64-bit Host Instance | shadabanwer

  5. how to handle exceptions in bulk insert i mean if i have 100 records and there is issue with data at 50th record of datatype mismatch which BizTalk is sending and which sql table is having, then what will happen till 50th record and what will happen to the rest 50 records. please suggest.

    • Hi Bala,
      Good Questions. What I think is it shall fail on your transformation to the SQL xml form or when you validate the message against the SQL generated schema?

      Regards
      Shadab

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s