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