BizTalk WCF-SQL Polling and de-batching on Receive Port(De-batching SQL Data)

Hi BizTalk Buddies,

Hope you are all doing great. Every one of us come across the scenario where we need to poll from the database. When we are polling, we can either can poll the single record at a time and process it or poll multiple records at a time and de-batch using Envelope schema on the receive port and Process the de-batched records individually. Here is a simple article that explains how to poll from the DB and de-batch on the receive port and subscribe to the Debatched Messages.

Below is the Employee Schema(Employee.xsd) that contains the Single Employee.
<?xml version="1.0" encoding="utf-16"?>
<xs:schema xmlns="http://BTS_SQL_Debatch.Employee" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" elementFormDefault="qualified" targetNamespace="http://BTS_SQL_Debatch.Employee" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="Employee">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="Name" type="xs:string" />
        <xs:element name="ID" type="xs:string" />
        <xs:element name="Salary" type="xs:string" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>
Below is the Employees Schema(Employees.xsd) that contains multiple employees that are polled from the database. Employee schema is imported to this Schema. Employees Schema has marked as an Envelope and the Body XPath is selected as shown below.
<?xml version="1.0" encoding="utf-16"?>
<xs:schema xmlns="http://BTS_SQL_Debatch.Employees" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" xmlns:ns0="http://BTS_SQL_Debatch.Employee" targetNamespace="http://BTS_SQL_Debatch.Employees" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:import schemaLocation=".\Employee.xsd" namespace="http://BTS_SQL_Debatch.Employee" />
  <xs:annotation>
    <xs:appinfo>
      <b:schemaInfo is_envelope="yes" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" />
      <b:references>
        <b:reference targetNamespace="http://BTS_SQL_Debatch.Employee" />
      </b:references>
    </xs:appinfo>
  </xs:annotation>
  <xs:element name="Employees">
    <xs:annotation>
      <xs:appinfo>
        <b:recordInfo body_xpath="/*[local-name()='Employees' and namespace-uri()='http://BTS_SQL_Debatch.Employees']" />
      </xs:appinfo>
    </xs:annotation>
    <xs:complexType>
      <xs:sequence>
        <xs:element ref="ns0:Employee" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Deploy the solution.

Once the solution is been deployed, We need to create One-way WCF-SQL receive Port as shown below.
Port Name:- rcv_Employees
Receive Location Name:- rcv_WCF-SQL_Employees
Type:- WCF-SQL
Receive Pipeline:- XMLReceive

In the Configure section please do the below settings.
Address(URI):- mssql://YourServerName//YourDatabaseName?InboundId=getEmployees
XmlStoredProcedureRootNodeName:- Employees
XmlStoredProcedureRootNodeNamespace:- http://BTS_SQL_Debatch.Employees
InboundOperationType:- "XmlPolling"
PolledDataAvailableStatement:-  SELECT COUNT(1) FROM [dbo].[Employee] WHERE IsPolled IS NULL AND DatetimeBizTalkPolled IS NULL
PollingStatement:-   exec [dbo].[getEmployees]

Rest of the properties you can leave then to the default values.

Save the above configuration. 

Now Create a FILE Sendport that subscribes on BTS.MessageType="http://BTS_SQL_Debatch.Employee#Employee"

Below are the scripts to create the table and the stored proc.

--Table
CREATE TABLE [dbo].[Employee](
[Name] [varchar](50) NULL,
[ID] [int] NULL,
[Salary] [decimal](18, 2) NULL,
[PollingId] [uniqueidentifier] NULL,
[IsPolled] [bit] NULL,
[DatetimeBizTalkPolled] [datetime] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Employee] ([Name], [ID], [Salary], [PollingId], [IsPolled], [DatetimeBizTalkPolled]) VALUES (N'Venu', 123, CAST(10.00 AS Decimal(18, 2)), NULL, NULL, NULL)
GO
INSERT [dbo].[Employee] ([Name], [ID], [Salary], [PollingId], [IsPolled], [DatetimeBizTalkPolled]) VALUES (N'Ravi', 124, CAST(34.00 AS Decimal(18, 2)), NULL, NULL, NULL)
GO
INSERT [dbo].[Employee] ([Name], [ID], [Salary], [PollingId], [IsPolled], [DatetimeBizTalkPolled]) VALUES (N'Raghu', 126, CAST(19.00 AS Decimal(18, 2)), NULL, NULL, NULL)
GO
INSERT [dbo].[Employee] ([Name], [ID], [Salary], [PollingId], [IsPolled], [DatetimeBizTalkPolled]) VALUES (N'Rakesh', 129, CAST(63.00 AS Decimal(18, 2)), NULL, NULL, NULL)
GO

--Proc
CREATE PROCEDURE [dbo].[getEmployees]
AS
BEGIN

DECLARE @pollingId UNIQUEIDENTIFIER
SET @pollingId = NEWID()

UPDATE TOP(10) [dbo].[Employee] SET PollingId = @pollingId, IsPolled=1, DatetimeBizTalkPolled= GETDATE() WHERE IsPolled IS NULL AND DatetimeBizTalkPolled IS NULL AND PollingId IS NULL;

WITH xmlnamespaces(DEFAULT N'http://BTS_SQL_Debatch.Employee')
SELECT [Name]
      ,[ID]
      ,[Salary]
  FROM [dbo].[Employee]
  WHERE PollingId=@PollingId
  FOR XML PATH('Employee'), TYPE
  END

We are done. If you Start the SendPort and Enable the Receive location, you should be able to see the 4 different files as shown below.

Hope you have enjoyed the article. Have a great day.

Please do comment if you liked the article.