Wednesday, 2 November 2011

WCF-SQL, BizTalk, notification and content based routing

Many people use TypedPolling when they are working with BizTalk and wanting to poll data from the SQL Server and publish it to BizTalk, but there is another way where you use notification.

This sample show how to use notification with the WCF-SQL adapter and BizTalk. It shows that you can use notifications without orchestrations to call a stored procedure in a SQL Server database (Adventureworks) and send output from this stored procedure into the file system. The execution of the stored procedure is only done when BizTalk receives a notification from the SQL Server about something has changed in a table.

The first thing that is needed is that the SQL Server you are working with has SQL Broker installed and it is configured for the database that you want to poll data from:

The next thing to do is to create a notification schema in your Visual Studio BizTalk project:
This will give you this schema:
The schema has 3 elements:
You should promote at least info and source element, so that you can use these fields for routing.

The next thing you need is to have a stored procedure that you can call to get the data from the SQL Server:

This procedure is just pretty simple and normally you would have some kind of logic to find out which rows that hasn't been polled from the table yet. It could be a datetime field, a boolean field or something else. There are plenty of examples of this on the net.

You then need to create a schema for this procedure, so that BizTalk knows how to call the procedure:
The next thing you need is a simple map to create the request message that is send to the wcf-sql adapter, so that it knows that you want to execute this procedure:
Now you just need to get the Visual Studio project build and deployed to the BizTalk Server. 

The BizTalk Server now needs to be configured to use the schemas and map that we have created in Visual Studio. The first thing is to setup a Receive Port and Location (remember to set the pipeline to XMLReceive):
The 3 properties that you want to set are (there are also some that you need to connect to the SQL Server, but you most likely know how to do that):
  • The "InboundOperationType" needs to be set to "Notification"
  • The NotificationStatement must have a select statement for the table you want to get notifications on "SELECT [DepartmentID] FROM [HumanResources].[Department]" in my example
  • NotifyOnListenerStart must be True or False. If it is True it will give you a message when the receive port starts
The next thing you need is a request/response port where we send a request to the SQL Server that we want to execute a stored procedure:
Set the outbound map that we created:
Set the filter to route everything we get from the receive port (notification). Normally you would use the promoted properties in the notification schema to filter exactly when you want to poll and everything else in a garbage port (you always need a subscriber):

Now we just need a send port to subscribe to the output that the stored procedures returns. In this case it is just a file port, but it could be anything like a LOB system:

That is it. We have created content based routing based on the WCF-SQL adapters notifcation feature. The advantage is you get to poll the data from you SQL Server at the moment the insert/update and you don't need to poll for available data every 30 seconds even if there isn't any data. The disadvantage is that you will get some garbage notifications that you need to handle somehow and I don't think I would use this method on high tables with a lot of transactions as you will get a lot of notifications on every event.

Random posts: