A simple BizTalk Server 2009 WCF SqlServer adapter Stored Procedure example

Hi all,

Continuing my delving into BizTalk 2009, ESB and WCF I have done a simple tutorial on how to insert a record into a SQL Server 2008 database using the new WCF Adapters. The new WCF Adapters are highly configurable and manageable and as such we recommend using them.

One important Gotcha: I had already installed the new ESB 2.0 guidance on my Windows Virtual PC. This meant that the GlobalBankESB database had already been created including a stored procedure that I wanted to call in my tutorial. This stored procedure was already being called from another tutorial project and as such there were already artifacts generated for it. In particular this was important: there was a record created in the BizTalkMgmntDb, table bt_DocumentSpec for the already generated artifacts related to the stored procedure and other project. When I used the same stored procedure to generate artifacts and use it in a different project I had a problem where the project wouldn't work. The following is the error that I received:
Event ID: 5719Description: There was a failure executing the receive pipeline: "Microsoft.BizTalk.DefaultPipelines.XMLReceive" Source: "XML disassembler" Receive Location: "C:\Projects\OrdersIn\*.xml" Reason: The disassembler cannot retrieve the document specification by using this type: "<MyDocName>". Either the schema is not deployed correctly, or more than one schema is deployed for the same message type.

How to get around this problem? First I had to undeploy from BizTalk the test project that I had made so that the double records within the bt_DocumentSpec table would be corrected. The next step was to create a copy of the stored procedure I wanted to use and rename it to something unique. Then I could regenerate the artifacts and redeploy and it worked. I looks as if you want to reuse this type of artifact within a BizTalk group you will have to generate your artifacts and then if you wish to reuse them you will have to share your project within your other projects to get access.

Simple BizTalk Server 2009 WCF SqlServer Adapter Example

Phase #1. The database, I had used the GlobalBankESB and storedprocedure "InsertProduct". I then had to make a copy of this procedure and named it InsertProduct2 (read section Gotcha above). Here is the table and stored procedure:

SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Product](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](100) NOT NULL,
[Description] [nvarchar](max) NOT NULL,
[Price] [money] NOT NULL,
[Quantity] [int] NOT NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE PROCEDURE [dbo].[InsertProduct]
@Name nvarchar(100),
@Description nvarchar(MAX),
@Price money,
@Quantity int
AS
BEGIN
INSERT INTO [dbo].[Product] ([Name],[Description],[Price],[Quantity])
VALUES (@Name,@Description,@Price,@Quantity)
END
GO

Phase #2. Visual Studio, Create a VS studio project to generate the artifacts we need to call the stored procedure from BizTalk.
My goal here is to pick up an external message, convert it into an internal message and have BizTalk write a record to the database using the new WCF SQL functionality. I ended up with the following items in my project:

1. a XSD that defines the external message. This I created myself: ProductExt.xsd
2. a XSD that defines the internal message in such a manner that we can use it write to the database (generated items)
3. a transform map that allows me to convert from the external to my internal. This I created myself: ProductExt_To_ProductInt.btm
4. an Orchestration so that I can control what's going on and more easily debug in the event of a problem
5. a WCF binding file (a generated and a critical item)
6. don't forget that all BizTalk projects need a strong name, example: sn.exe -k NoGiWCFSQL.snk

Step 1. Create the project and add ProductExt.xsd.

Step 2. Generate Artifacts

Right click the project and select Add > Add Generated Items.

A browser window will open, select ConsumeAdapterService

Select ConsumeAdapterService

This will reveal the Consume Adapter Service window:

Select a binding: sqlBinding

Configure a URI: mssql://<yourServer>/<yourInstance>/<yourDB>? (this connection URI is quite different that the usual connection strings that we expect with a SQLServer database)

Connect: If your connection status is not Connected recheck your URI

Select a category: I selected ‘Procedures’ as I want to call a stored procedure.

Available categories and operations: notice that there are 2 records here in this list, InsertProduct and InsertProduct2. This is the Gotcha that I spoke about. I originally tried to generate artifacts for the InsertProduct record (InsertProduct was not even present at the time) and that was successful. When I deployed my project however the processing failed. After quite some time debugging and searching I discovered that I had generated the same artifacts and deployed as were already done in an ESB tutorial. In the bt_DocumentSpec table there were double records present for the InsertProduct object. I am not sure if this is a bug or an undocumented hidden feature. To overcome this quickly I duplicated the InsertProduct stored procedure and appended it with 2. A better solution perhaps would be to reuse the already present artifacts but I thought this was interesting enough to write about it so that others can avoid it.

Click Ok

Visual Studio will now add a number of Artifacts to your project (depending upon what you have chosen to generate). In my case I had the following:

1. DataSetSchema.xsd

2. Procedure.xsd

a. Defines a Request Insert object

b. Defines a Response object

3. A binding file: WcfSendPort_SqlAdapterBinding_Custom.bindinginfo.xml containing the information that I needed to create a Request / Response call for within my orchestration

Step 3. Create an Orchestration and transform map to insert the records.

The external message is picked up and then transformed to my internal representation defined by the Procedure.xsd Request object. The advantage of an Orchestration from a development point of view is that you can debug much more easily and add System.Diagnostics.EventLog.WriteEntry(“<myEntry>”) types of items to help you. After the record has been inserted a response comes back and this file gets placed in an ‘archive’. Note that the 3 ports are defined as logic ports

1. PortProductExt (always receiving)

2. PortReqRespInsertWCFSQL (request/response)

3. PortSendProductArchive (always sending)

Also note that

1. msgProductExt defined by my ProductExt.xsd

2. msgProductReq defined by the Procedure.xsd > request object

3. msgProductResp defined by the Procedure.xsd > response object

Step 4. Compile and deploy.

Notice now that there is an orchestration in the project and that it requires a Receive port, Request/Response port and a send port.

The first step is to bind the ports as follows:

1. Receive Port (and location) for ProductExt: C:\projects\ProductIn, File adapter, *.xml

2. Send Port for PortSendProductArchive: C:\projects\Archive, Archive_%MessageID%.xml

3. Send / Receive Port for the PortReqRespInsertWCFSQL: this is a nice trick and reveals the power of the WCF Adapters. Right click on the BizTalk project that you have deployed and select Import > Bindings. Browse to the xml (binding) file that was added to the project. In my case that was: WcfSendPort_SqlAdapterBinding_Custom.bindinginfo.xml and select it. This will configure the WCF port for you, awesome.

4. Ensure that all the ports have been bound properly

5. Start the BizTalk application and test it by dropping an instance of the ProductExt into the C:\projects\ProductIn folder. 

Note: WCF-Custom is selected as the type.

Note: the binding is sqlBinding and within the SOAP Action header there is a reference to my InsertProduct2 operation.

A record should have been created in the Database product table and an archive of the response message in the Archive folder!