You are here:   Home
  |  Login

BizTalkBill's Ramblings

BizTalk 2010 XML Polling with WCF-SQL Adapter

Apr 5

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

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)

image

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.

image

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:

image

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

image

Build and deploy your solution.

In BizTalk Administration Console

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

image

 

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)

SNAGHTML13f2d2e

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)

SNAGHTML140cd14

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)

SNAGHTML1444520

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

SNAGHTML146fad8

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)

SNAGHTML15574d8

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


SNAGHTML157e65f

Click Ok to close the Send Port Properties dialog

 

Start the application

Right click on the Application and select start

image

 

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.

SNAGHTML163bd0e

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

Tags:
Categories:

18 comment(s) so far...


Gravatar

Re: BizTalk 2010 XML Polling with WCF-SQL Adapter

Bill,

Thanks for tackling this. There's a very limited number of correct examples in this vein. One thing I ran into was a whole bunch of difficulty trying to install the AdventureWorks DB. After downloading from codeplex I updated the path in the instawldb.sql. Make sure the csv are placed in the proper directories and then make sure you set SQLCMD mode in SSMS (Query -> SQLCMD Mode).

Thanks again Bill. Great contribution!

\cbf

By c.b.fernald on   4/19/2012 12:44 PM
Gravatar

Re: BizTalk 2010 XML Polling with WCF-SQL Adapter

One more comment: in your description for configuring the Send Port you mention the following entry on the Filters tab:

BTS.ReceivePortName == AdventureWorks.Orders.Polling

Just for clarity, this action defines a subscriber for the Receive Port. Without it, you'll get the error:

"The published message could not be routed because no subscribers were found. "

That is all.

Thanks!


By c.b.fernald on   4/20/2012 10:17 AM
Gravatar

Re: BizTalk 2010 XML Polling with WCF-SQL Adapter

Using the Add Generated Items we can generate the Xml which mapped to the procedure. Can not we use that as our schema here

By Udaya on   6/23/2012 12:31 PM
Gravatar

Re: BizTalk 2010 XML Polling with WCF-SQL Adapter

I don't think so, does your stored procedure have a for xml clause in it, then maybe.

I will try it with my example stored procedure and get back to you.

By Bill Chesnut on   6/24/2012 7:37 AM
Gravatar

Re: BizTalk 2010 XML Polling with WCF-SQL Adapter

Hi,
how do I insert an XML into SQL DB with the following details :

SQL server - 2008 , Biztalk 2010

Insert_Into_SQL_Schema
Stored procedure
Custom send pipeline


XML Message arrives --> received by receive pipeline --> picked by Send Pipeline having Custom Pipeline code with it.

message fails to load in DB with an error in EventViewer:

Root namespace and target namespace mismatch error (something like that).

By Manjunath on   6/30/2012 5:06 AM
Gravatar

Re: BizTalk 2010 XML Polling with WCF-SQL Adapter

My stored procedure is as follows(Sample procedure)
SELECT * FROM Student FOR XML AUTO, XMLDATA

By Udaya on   7/4/2012 10:39 PM
Gravatar

Re: BizTalk 2010 XML Polling with WCF-SQL Adapter

Everything is good in this example.but in sqlserver u did not create table name but created stored procedure, how to insert data in database to poll xml message in biztalk.Please send me the query of stored procedure execution

By chinnasrinivasarao on   7/18/2012 11:04 AM
Gravatar

Re: BizTalk 2010 XML Polling with WCF-SQL Adapter

this article was about polling XML from the database, not inserting XML in the database, try the Microsoft BizTalk forums.

By Bill Chesnut on   7/18/2012 11:50 AM
Gravatar

Re: BizTalk 2010 XML Polling with WCF-SQL Adapter

Have you downloaded the sample code for this article?

Not exactly sure what you are asking for, do you want the code for the stored procedure or the insert statement to add records to the table?

By Bill Chesnut on   7/18/2012 11:52 AM
Gravatar

Re: BizTalk 2010 XML Polling with WCF-SQL Adapter

Everything is good in this example.but in sqlserver u did not create table name but created stored procedure, how to insert data in database to poll xml message in biztalk.Please send me the query of stored procedure execution

By chinnasrinivasarao on   7/18/2012 12:22 PM
Gravatar

Re: BizTalk 2010 XML Polling with WCF-SQL Adapter

i need this query how to insert statement to add records to the table?

By chinnasrinivasarao on   7/18/2012 12:27 PM
Gravatar

Re: BizTalk 2010 XML Polling with WCF-SQL Adapter

i need this query how to insert statement to add records to the table?

By chinnasrinivasarao on   7/18/2012 12:28 PM
Gravatar

Re: BizTalk 2010 XML Polling with WCF-SQL Adapter

To test I just reset the status to 5, I did not insert any new records, just used the existing records

Update [Sales].[SalesOrderHeader] set [status] = 5

By Bill Chesnut on   7/18/2012 12:30 PM
Gravatar

Re: BizTalk 2010 XML Polling with WCF-SQL Adapter

Hi Bill,

Does it matters with the volume of the data? How the performance will be impacted?
I am talking about almost 1 million records from DB and each reacord will have 15 nodes at a minimum.
The DB will return me the raw data nd I have to format them using some XSD before passing on to the send port.

I am not planning on any orchestration as I want to save as mush time as I can before passing on the formatted XML file to the send port.

Any thoughts.

Thanks in advance
Sandeep Mishra

By Sandeep Mishra on   5/7/2013 6:43 AM
Gravatar

Re: BizTalk 2010 XML Polling with WCF-SQL Adapter

Yes, creating XML in the database for 1 million records will have performance issues, but I would never use BizTalk to extract 1 million records out of a database I would use SSIS, BizTalk is a message processing tool, not a batch import/export tool.

By Bill Chesnut on   5/7/2013 9:12 PM
Gravatar

Re: BizTalk 2010 XML Polling with WCF-SQL Adapter

Very nice example on polling. Concise and to the point. Much appreciated!

By heavymetal on   10/14/2013 8:28 AM
Gravatar

Re: BizTalk 2010 XML Polling with WCF-SQL Adapter

Can you comment on why one would choose a Wcf-Custom receive location with sqlBinding vs. the Wcf-Sql receive location?

By Frank on   3/8/2014 5:45 AM
Gravatar

Re: BizTalk 2010 XML Polling with WCF-SQL Adapter

There are 2 things, if you forget to add the WCF-SQL when you install the adapter pack, the WCF-Custom still works and all the tools generate WCF-Custom bindings. With some other protocols, there are more setting available with WCF-Custom, but with the WCF-SQL the same setting are available either way.

By Bill Chesnut on   3/11/2014 3:30 PM

Your name:
Gravatar Preview
Your email:
(Optional) Email used only to show Gravatar.
Your website:
Title:
Comment:
Security Code
CAPTCHA image
Enter the code shown above in the box below
Add Comment   Cancel 

Text/HTML













\




MCP.gif MCT.gif MCPD(rgb)_506.gif
MCTS_511.gifMCTS(rgb)_1271.gifMCTS(rgb)_1325.gif