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.