Apr 5

Written by:
4/5/2012 3:08 PM 

One of the feature that came with the WCF-SQL Adapter was the ability to XML polling from SQL.  Anyone that remembers the original SQL adapter from BizTalk remembers the requirement for all the data returned from SQL to have the FOR XML AUTO added to the end of the query and the XMDATA added to add the Store Procedure call to your BizTalk project.

Typically when you poll data out of SQL it is a single table or the output of a stored procedure, this limited the type of data that could be returned, the example is that it was difficult to return the order header and all the detail lines with a single polling statement.  The XML polling feature of the WCF-SQL adapter allow you to create your own XML document in SQL and return it to BizTalk via a polling statement.

In this blog post I am going to show how to do this.

Background

I will be using the Sample AdventureWorks database and returning and Order XML document.

Install the AdventureWorks SQL sample database from http://sqlserversamples.codeplex.com/

Install the BizTalk 2010 LOB Adapter SDK and the LOB Adapter Pack (in not already installed)

In SQL

Create a SQL Stored Procedure to return the desired data, using the xmlnamespaces and for xml path.  You will need to decide when you create your stored procedure whether it will be returning a single Order or a batch of Orders, there will be not difference to the XML generated in the stored procedure just a difference in how you select the records to return.  The stored procedure that I have created returns up to 5 Orders per call.

(complete SQL Stored Procedure available in download below)

Grant the BizTalk Application User group access to the AdventureWorks database and execute privileges to the GetSalesOrdersToProcess stored procedure.

Execute the stored procedure to create a sample XML document and save it to order.xml

In Visual Studio

Create a BizTalk Project (I am using Demo.WCFSQL.Polling for the solution name and AdventureWorks.Schemas for the project name)

In the BizTalk project add a generated item and select generated schema, in the generated schema dialog select Well-Formed XML, then select the order.xml file saved above.  This will create an order.xsd in your BizTalk project.  You may also need to update any numeric fields to the correct data types, the generated schema wizard sets number to the smallest data type that fits the sample data.

If you decide when creating your stored procedure to return multiple orders per execution, you will need to create and orders.xsd envelope schema, like this:

And set the Body XPath to the root node Orders.

Strong name your BizTalk project and set the deployment application name (I am using Demo.WCFSQL.Polling)

This is the complete Solution

Build and deploy your solution.

In BizTalk Administration Console

Expand the application that you deployed your solution into (I am using Demo.WCFSQL.Polling)

 

Create Receive Port and Receive Location

You now need to create the one-way WCF-SQL receive port and receive location (I am using AdventureWorks.Orders.Polling as the receive port name and AdventureWorks.Orders.Polling.WCFSQL as the receive location name)

The receive location can either be created as a WCF-Custom with the sqlBinding or as a WCF-SQL adapter (I am using the WCF-Custom with the sqlBinding)

After selecting WCF-Custom, click Configure.

On the General Tab type in the Address (URI), this consist of the protocol, server name, database name and Inbound Id like below:

mssql://<servername>//<databasename>?InboundId=Orders (my example looks like: mssql://.//AdventureWorks?InboundId=Orders)

On the Binding Tab, select sqlBinding in the Binding Type drop down list

Set the following setting

Key	Value	Comment
inboundOperationType	XmlPolling
polledDataAvailableStatement	select count(*) from [Sales].[SalesOrderHeader] where [status] = 5	This would be specific to your situation, this statement queries your table to see if any records are available to retrieve
pollingIntervalInSeconds	30	how often to try to retrieve data
pollingStatement	exec [dbo].[GetSalesOrdersToProcess]	this calls the stored procedure to retrieve data
xmlStoredProcedureRootNodeName	Orders	this is only necessary if you chose to bring back more than one order per execution
xmlStoredProcedureRootNodeNamespace	http://AdventureWorks.Schemas.Orders	this is only necessary if you chose to bring back more than one order per execution

Note: useAmbientTransaction defaults to True, if MSDTC is not configured between BizTalk and the source SQL Server you will have to set this to false

Click Ok, to close the WCF-Custom Transport Properties dialog

Select the appropriate Receive handler and set the Receive Pipeline to XMLReceive, then click OK to close the Receive Location Properties

 

Create Send Port

For this example we are just going to send the XML message directly out to a file location

You now need to create a one-way send port (I am using AdventureWorks.Orders.Send.FILE for the send port name)

Select a file location for the output XML messages (I am using C:\Working\Demo.WCFSQL.Polling\Out\%MessageID%.xml)

On the Filters tab, set the following filter: (this is for my example, yours may differ)

BTS.ReceivePortName == AdventureWorks.Orders.Polling

Click Ok to close the Send Port Properties dialog

 

Start the application

Right click on the Application and select start

 

Check the Output directory

You will notice with my example, 5 files are create at a each polling interval in the directory and that each file represents an order.

Note: there are over 31,000 orders in the AdventureWorks database.

 

Summary

I hope this example give you the information that you need to get started with XmlPolling with BizTalk and the WCF-SQL Adapter.

 

Code for this Sample

 

 

 

 

 

Copyright ©2012 Bill Chesnut