Using the BizTalk WCF-SQL Adapter to load a flat file into a SQL Server 2008 table

Posted: June 29, 2009  |  Categories: BizTalk Uncategorized

While preparing for my webcast on the WCF-SQL Adapter as part of a series of webcasts* I ran into a requirement that is quite frequent with BizTalk solutions: merging the contents of a flat file with a SQL Server database table. I wrote a post a while ago on how call the same stored procedure multiple times without a loop shape with the ‘old’ BizTalk SQL adapter, and this post also covers that with the new WCF-SQL Adapter. The source code will be made available as soon as the webcasts are out, and I’ll update this to reflect that.

I have a flat file that contains a list of products. I need to load the contents of this flat file into a SQL Server 2008 table using BizTalk Server 2009 and the WCF-SQL Adapter. The table might already have some of the products in the flat file, and in this case the product row should be updated.

The data in my sample flat file was extracted from the Adventure Works sample database in the SQL Server 2008 samples in Codeplex, which gave me 504 products to play with.


Flat File and Debatching

As you might already know, messages with multiple items in them (multiple Products in this case) coming into BizTalk can be disassembled and debatched on their way in by the disassembler pipeline components. In this case, since it is a flat file that we are receiving, we will use the flat file disassembler component that comes out of the box with BizTalk.

In this post I want to go over loading the file into SQL Server in two ways: one by splitting the Product items into individual messages and loading them individually with the WCF-SQL Adapter; and another by not splitting the Product items and sending one single message to the WCF-SQL Adapter with all the products.

For that I created two flat file schemas, and both look like the below:


On one of the schemas the Product node has its ‘Max Occurs’ set to ‘unbounded’. The other schema has the Product node’s ‘Max Occurs’ set to 1.  This property is what tells the flat file disassembler pipeline component if it should debatch the Products or not.

I created two BizTalk pipelines to handle the two different schemas. I dragged the flat file disassembler pipeline component to the disassemble stage of each pipeline, and selected the appropriate schema for each.

image image image


SQL Server Table and Stored Procedure

On the SQL Server database side, we have a table called Product (what a surprise!) with the following columns:


We are going to call a stored procedure for each line in the flat file to load each product. An easy way to either insert the product if it doesn’t exist in the table or update it if it exists is to use the MERGE statement that is new in SQL Server 2008. So all we have in our stored procedure is the following:


@ProductShortDescription varchar(50), @ProductFullDescription varchar(max), @UOM nchar(10), @UnitPrice money



–Use merge statement to either insert or update product based on product short description

MERGE INTO Product AS Target

USING (SELECT @ProductShortDescription, @ProductFullDescription, @UOM, @UnitPrice)

AS Source(ProductShortDescription, ProductFullDescription, UOM, UnitPrice)

ON (Target.ProductShortDescription = Source.ProductShortDescription)

WHEN matched THEN

UPDATE SET ProductFullDescription = Source.ProductFullDescription, UOM = Source.UOM, UnitPrice = Source.UnitPrice

WHEN not matched THEN

INSERT (ProductShortDescription, ProductFullDescription, UOM, UnitPrice)

VALUES (Source.ProductShortDescription, Source.ProductFullDescription, Source.UOM, Source.UnitPrice);


WCF-SQL Adapter Schemas

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’. From there you can either choose the ‘Add Adapter Metadata’ or the ‘Consume Adapter Service’ options. They will both bring the ‘Consume Adapter Service’ wizard where you can connect to the target SQL Server database and select what items and operations you want to consume. In our case we are only interested on the ADD_PRODUCT strongly typed stored procedure:


This will give you a schema like the following for the ADD_PRODUCT stored procedure:


Note that the ADD_PRODUCT node is the root node, and therefore can only exist once in the XML instances for this schema. This is the schema we are going to map to for the debatched Product information we get from the flat file schema with a max occurs of 1.  It is a straight map then from the single product flat file schema to the single stored procedure schema:


That takes care of mapping the products when the flat file is being debatched into single product messages. Now what do we do about mapping all the products in the flat file to only one XML that is sent to the WCF-SQL Adapter? Here’s where the WCF-SQL Adapter’s 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. I created a new schema with a root node ‘Request’ and a second root node ‘RequestResponse’. The first root node name isn’t really important, as long as the second root node name is the same as the first with a ‘Respose’ suffix. I then added the single ADD_PRODUCT schema as an XSD Import to my composite schema. This allows me to create an unbounded record under the ‘Request’ node and change it to have a data structure type of ns0:ADD_PRODUCT, and an unbounded record under the ‘RequestResponse’ node and change it to have a data structure type of ns0:ADD_PRODUCTResponse.


This allows us to map from the non debatched flat file schema to the schema created above:



Calling the WCF-SQL Adapter

After deploying the solution I created two receive ports and two respective receive locations – one of them configured with the debatching pipeline and the single product map, and the other configured with the single file pipeline and the composite operation map.

I then created two one way send ports with the WCF-Custom Adapter and the sqlBinding, each with a filter for one of the receive ports. The send port that filters on the debatched single product insert receive port is configured as follows, with the TypedProcedure/dbo/ADD_PRODUCT action:


The send port that filters on the single file with multiple products and composite operation map is configured as follows, with the CompositeOperation action:


Both  send ports had a binding type of sqlBinding of course, with the default values (make sure useAmbientTransaction is enabled so that the stored procedure calls are inside a transaction):



Transactions and Conclusion

So when we debatch the Products flat file on the way in we end up with multiple concurrent calls to the stored procedure via the WCF-SQL Adapter, each in its own transaction:


When we map the entire file to the composite schema we end up with one transaction that wraps around all the stored procedure calls:


If we monitor the Transactions/sec for the database we see barely any activity when we use the single file method:


If we use the debatch multiple message method we some spikes as the multiple transaction to the database are made:


As expected the single file method performs much faster for loading the 504 rows into the table. By placing a datetime column on the products table I could see the difference from the first insert to the last is only 254 milliseconds. With the debatch method BizTalk goes through the debatched records at a much slower pace  taking around 16 seconds to load them all, since it has to map each debatched message, route multiple messages to the send port, create multiple transactions against SQL Server, etc.

After looking into it a bit more I also noticed that for the debatched scenario the message delivery throttling and message publishing throttling were kicking off for the BizTalk host loading the messages into SQL Server. By simply changing the number of samples that the host should base its throttling decision on to something over the 504 records being inserted the time for the debatched inserts went down to 4 seconds from the 16 seconds mentioned above:

image image

The debatch method is still useful in many situations – if you need to perform extra steps for each message in the batch, or if your DBAs require one transaction for each stored procedure call, etc.

As I mentioned at the beginning, the source code will be made available as soon as the BizTalk 2009 webcasts are out, and I’ll update this post to reflect that.


Links for this blog post and the full SQL Adapter webcast: Video, Slides, Source Code



Thiago Almeida


* BizTalk 2009 Light and Easy Webcast SeriesMick Badran asked many BizTalk/CSD MVPs and Industry experts (around the globe) to share their knowledge and expertise. We came up with a series of Webcasts/Presentations and Demo Code for the community around many aspects of BizTalk 2009 – from new features in BAM, SharePoint, SQL Adapter… to Orchestration Performance. Brilliant! Stay tuned as the bits are being finalized.

17 thoughts on “Using the BizTalk WCF-SQL Adapter to load a flat file into a SQL Server 2008 table”

  1. Interesting. I’ve built a similar project based on SQL 2008 stored proc by also utilizing WCF-SQL adapter then ran into your article.
    In addition, I utilized correlation type/sets along with .snk file when deployed the solution. BTW, when I configured send port as WCF-Custom and sqlbinding I decided to copy the correct URI and Action (shown on WCF-Custom General tab) based on Configure URI value along with Node ID under Added categories/operations shown when you add a Consume Adapter Service (Visual Studio Project > Add Generated Items > Consume Adapter Service). This way I avoid mispelling or any configuration errors since you have to manually type both URI and Action when configure send port as WCF-Custom from BizTalk 2009 Administration mmc.

      1. Can you please send me the sample of load Text file to database based on the name of the flat file.
        If file name starts with Students, data needs to go in student table.
        If file name starts with teachers, data needs to go in Teachers Table.

        Please help me ASAP 🙁

  2. Can you please send me the sample of load Text file to database based on the name of the flat file.
    If file name starts with Students, data needs to go in student table.
    If file name starts with teachers, data needs to go in Teachers Table.

    Please help me ASAP

  3. Excellent article demostrating wcf-SQL Binding capabilities & BizTalk’s overall performance imporvement as well.
    I have used the native SQL Adapter to load data in SQL table using Updategrams (as per posts on Stepen Kuafman’s blog),
    the native SQL Adapter works fine but has certain limitations. We had to use it as teh new wcf-sql adpater was still not out.
    but this new approach using WCF-SQL adapter is better that native SQL adapter.
    I am eager to know if UpdateGrams are still supported in WCF-SQL adapter
    the reason is : – updategrams using native SQL Adapter can load relational data i.e. in mutiple tables and it would awesome if you can share your
    views how to handle multiple table inserts using the new & imporved WCF-SQL Binding Approach. thanks a lot.

    1. Hi there. Thank you!
      The new adapter does not work with updategrams. Updategrams were part of the SQLXML product that the old adapter took advantage of. The new adapter does not use that, it uses ADO.NET directly.
      From BizTalk the only method I’ve seen where you can perform inserts into multiple tables that are related to each other is described here by using table types and table-valued parameters:


  4. Great post and I knew this functionality was available in BizTalk and the FF Assembler. Why I found your post was the following, I am looking for a way to break up a Huge Batch into smaller pieces…. So instead of 1 record each time I set the maxOccur to 20 (just to see what was happening), unfortunately the debatching only happens when it is set to 1. Do you know why, or know a trick how to bypass this limitation.

    1. Hi Patrick, going through some comments in the blog and found yours, sorry I missed it before. The only way I have found to do this is to do it in a custom disassembler pipeline component. It isn’t very straight forward but if you do it correctly following the best practices of using streams it works well.

  5. Hi, I followed same link, I have small issue, i am using xml as receive input instead of flat file
    Same products schema conating multiple product, however while processing this only inserting first node. Any idea how to insert allnode into databse

  6. Hi, I have followed same example, I am using input as xml with multiple record and inserting via compositeaction, I made envolope schema with unbounded as mention in example. I used loop functoid for Prouct Node as depicted in image.

    This only inserting first node into database, I have genrated xml as well I can see only first node is coming ? Can you please suggest me where things went wrong ?

  7. Hi All,

    Can someone please post the step by step Screen shots for this please…
    It would be a great help for me.
    Thanks in Advance

Comments are closed.

BizTalk Server

Over 500+ customers across
30+ countries depend on BizTalk360

Learn More

Manage and monitor serverless
components effortlessly

Learn More
Business Users

Monitor your Business Activity in iPaaS
or Hybrid integration solutions

Learn More

Back to Top