Geeks With Blogs

News
Stuart Brierley Integration Management - BizTalk and More

BizTalk Server 2010 - Using the WCF-SQL adapter to make multiple stored procedure calls

A recent project that I was working on required that I take an incoming flat file, containing multiple transaction records, and load these transactions into a database.

Within the source file were multiple instances of any of seven transaction types.  The database had a separate table for each transaction type, each with its own stored procedure for inserting a record.  So thats seven transaction types and seven stored procedures.

Thiago Almeida posted in 2009 on Using the BizTalk WCF-SQL Adapter to load a flat file into a SQL Server 2008 table, where he demonstrated two methods for calling a single stored procedure multiple times:

  • Debatching - splitting the source message into separate messages and loading them individually with the WCF-SQL Adapter
  • Single file - not splitting the source message, sending one single message to the WCF-SQL Adapter

In this posting he was able to show that:

"....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."

Given the improved performance of the single file approach, I wondered if this approach could be built on to call all seven of my target stored procedure in a single transaction.  Detailed below is a simplified example of how to call multiple stored procedures in a single transaction.

The Source Schema

The Stored Procedures

Creating the WCF-SQL Schema

First you need to add the SQL Server schemas used by the WCF-SQL Adapter. In Visual Studio, 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.  Ultimately both options will lead you to the Consume Adapter Service’ wizard, but selecting "Add Adapter Metadata" will first present you with the Add Adapter Wizard, where you should select WCF-SQL; doing this will pre-fill the binding option in the Consume adapter Service wizard which you would otherwise have to select yourself! 

Selecting "Consume Adapter Service" will open 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.

You will then need to carry out the following steps:

  • Configure the URI of the target database, using the form "mssql://ServerName:port/InstanceName/Databasename". 
    • In my case I am looking at a local database with only the default instance,  so I put "." as the database name and no instance name.
  • Select Connect.
  • You should then see a list of outbound operations in the Select a Category area.  Select Procedures.
  • You should then see the stored procedures in the Available Categories and operations.
  • Select all stored procedures and press Add.
  • If required check the generate unique schema types checkbox and enter a Filename Prefix.
  • Select OK.

This results in the following schema:

Although this has created a schema containing nodes for each of stored procedure requests (and their respective responses) all are at the root level and therefore only once instance of one of these nodes can exist in the XML instances for this schema.

Creating the Request, RequestResponse message

To create a schema that allows us to make multiple calls to multiple stored procedures, we need to make use of the WCF-SQL Adapter’s composite operations.

You will need to create a new schema with two root nodes, 'Request' and 'RequestResponse'; the name of the request node isn’t really important as long as the corresponding response node name is the same as the first with a ‘Respose’ suffix.

You then need to add the generated schema from earlier as an XSD Import to this new composite schema. 

Under the ‘Request’ node create an unbounded record for each of the stored procedure you want to call, changing each to have the appropriate request data structure from the imported generated schema.

Under the 'Response' node create an unbounded record for each of the stored procedure you want to call, changing each to have the appropriate response data structure from the imported generated schema.
 

Map Source to Request

This allows us to map from the source schema to our multiple procedure call schema.  When adding the destination schema you will be prompted to select the root node.  In this case select Request, if you need to map from the response at a later date you should obviously select RequestResponse instead:

For each instance of a Return, Sale or SpoiltStock record in the source message, the following map will result in a corresponding stored procedure request in the destination message.

For example, using the source message:

Results in the destination message:

WCF-Custom Adapter Send Port Configuration

After deploying the solution and creating an appropriate receive port and location for the source message, you will need to create a send port using the WCF-Custom Adapter and the sqlBinding.


Set the address URI to you target database, and the Soap Action Header to "CompositeOperation":
 

Set the Binding Type to "sqlBinding" and use the default values, making sure that "useAmbientTransaction" is enabled.  This ensures that the multiple stored procedure calls are inside a single transaction:

When you submit your request message it should now be mapped into a single request containing multiple stored procedure calls, which will hit the target database in a highly efficient single transaction. 

For the live example I cited earlier (seven transaction types with seven different possible stored procedures) each night this approach is being used to process around 15,000 stored procedure calls in a single transaction.  So far it has proven to be robust and reliable, taking less than a minute to complete this call.
 

 

Posted on Wednesday, October 19, 2011 5:18 PM BizTalk Adapters , BizTalk Maps, Mapping and Functoids | Back to top


Comments on this post: BizTalk Server 2010 - Using the WCF-SQL adapter to make multiple stored procedure calls

# re: BizTalk Server 2010 - Using the WCF-SQL adapter to make multiple stored procedure calls
Requesting Gravatar...
excellent post, but what if a schema node is optional...how do you get around the " Table-valued parameters cannot be DBNull." errors?
Left by Keith Calef on Nov 29, 2011 7:50 PM

# re: BizTalk Server 2010 - Using the WCF-SQL adapter to make multiple stored procedure calls
Requesting Gravatar...
Thanks for posting this. I came across Thiago's article first and noticed it didn't quite work the same way in 2010. Eventually found found your post. Works great, cheers!
Left by Keith Knight on Feb 09, 2012 5:24 AM

# re: BizTalk Server 2010 - Using the WCF-SQL adapter to make multiple stored procedure calls
Requesting Gravatar...
Great blog, this has helped me loads. Previously I would just dump everything into a temp table and then execute an SP which would move the data where it needs to go but this is far more elegant.

One question though - I can't get it to work consistently with useAmbientTransaction set to true. The transaction never seems to finish although in the SQL Server Profile shows that all the commands have run. Any idea what I might be doing wrong?

Seems like I'm not the only person having this issue.

http://social.msdn.microsoft.com/Forums/en/biztalkr2adapters/thread/8baa94ac-d716-4304-bbde-b7f17fec05b4

I've tried installing the Cumulative Update packs but no joy.
Left by Stuart Charles on Jul 04, 2012 10:45 AM

# re: BizTalk Server 2010 - Using the WCF-SQL adapter to make multiple stored procedure calls
Requesting Gravatar...
I think I can answer my own question. There seems to be some kind of problem with the message box database in my development environment as when I run this process I get a warning in the event log saying

The following stored procedure call failed: " { call [dbo].[bts_UpdateMsgbox_SendHost]( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}". SQL Server returned error string: "Warning: The join order has been enforced because a local join hint is used.;Warning: The join order has been enforced because a local join hint is used.;Duplicate key was ignored.".

I originally didn't link the error to my problem as it didn't really seem relevant. But I am pretty sure this error is causing my entire transaction to fail or at least hang.

Reading around it seems linked to performance issues on the SQL database which is more than feasible in my modest dev environment.

I resolved it by turning off tracking which must lower the load on my server. Others also claim that running the stored proc bts_CleanupMsgbox also helps as per below.

DECLARE @RC int
DECLARE @fLeaveActSubs int

-- TODO: Set parameter values here.

SET @fLeaveActSubs = 1

EXECUTE @RC = [BizTalkMsgBoxDb].[dbo].[bts_CleanupMsgbox]
@fLeaveActSubs
GO
Left by Stuart Charles on Aug 07, 2012 9:17 AM

# re: BizTalk Server 2010 - Using the WCF-SQL adapter to make multiple stored procedure calls
Requesting Gravatar...
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.
Left by vaibu on Oct 29, 2013 8:21 PM

# re: BizTalk Server 2010 - Using the WCF-SQL adapter to make multiple stored procedure calls
Requesting Gravatar...
Hi, did you manage to try the rename approach out?

I haven't had to do this with the WCF-SQL adapter, but in the past with the standard SQL adapter I have made changes to the schema that represents the stored procedure to match changes in the stored procedure (extra parameters, procedure name change) so I would think this should have a high chance of working.
Left by Stuart brierley on Mar 05, 2014 3:40 PM

# re: BizTalk Server 2010 - Using the WCF-SQL adapter to make multiple stored procedure calls
Requesting Gravatar...
<Employee>
<DeptName>DEV</DeptName>
<EmpName>Anil</EmpName>
<EmpNo>101</EmpNo>
</Employee>

<AllDepart>
<Department>
<DeptNo>1</DeptNo>
<Dname>Sales</Dname>
<Location>Vij</Location>
</Department>
</AllDepart>
</ArrayOfEmployee>
Left by anil on Jul 08, 2014 7:25 PM

Your comment:
 (will show your gravatar)


Copyright © Stuart Brierley | Powered by: GeeksWithBlogs.net | Join free