How to poll database using WCF-SQL adapter
2. Create stored procedure
3. Create BizTalk project
4. Create orchestration
5. Consume WCF-SQL
6. Write received data to file share
First create TESTWCFSQL database from you SQL Management studio
Run the script to create table:
USE TESTWCFSQL
GO
IF OBJECT_ID('dbo.MainData', 'U') IS NOT NULL
DROP TABLE dbo.MainData
GO
CREATE TABLE dbo.MainData
(
MainDataID int primary key identity(1,1),
FirstName nvarchar(30),
LastName nvarchar(30),
Amount int
)
GO
Run script in SQL management studio to create procedure
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo'
AND SPECIFIC_NAME = N'PollData'
)
DROP PROCEDURE dbo.PollData
GO
CREATE PROCEDURE dbo.PollData
AS
select
MainDataID,
FirstName,
LastName,
Amount
from dbo.MainData
delete from dbo.MainData
GO
insert some sample lines executing
use TESTWCFSQL
insert into MainData (FirstName, LastName, Amount) values
(N'Patrick', N'Cash', 1000),
(N'Ivan', N'Landle', 2000),
(N'Pitt', N'Sampras', 3000)
To test procedure run
exec dbo.PollData
but do not forget to run insert one more time
Now we have ready to run SQL table and procedure
Start Visual Studio 2008 in "Run as administrator" mode
From Visual Studio 2008 create new BizTalk project, name it
WCFSQLTest
Insert Item from "Add generated item"
From templates select "Add adapter metadata"
Then select WCF-SQL
In Binding select "sqlBindng"
Press "Configure"
for parameters select
Security
Client Credential Type: Windows
Client Credential Type: Windows
URI properties
InboundID: ID1
Server: localhost
Initial catalog: TestWCFSQL
Binding properties
Inbound operation type: TypedPolling
Poll data available statement: select top(1) * from MainData
Polling statement: exec dbo.PollData
Press OK
Press Connect
Check that you connected to DB
select Service(inbound operations)
in Select a category choose TypedPolling than Add dbo.PollData
Press OK
Schema file and orchestration file will be generated
Rename orchestration WCFSQLOrchestration1.odx
Rename orchestration WCFSQLOrchestration1.odx
Rename schema definition file to InboundSchema.xsd
In orchestration create message, name it SQLPoll
In orchestration create message, name it SQLPoll
Set message type: To multipart message type which was auto created
On PortSurface start Port Configuration Wizard
Name new port: SQLRcvPort
press Next
select Use existing Port Type
select port type created by wizard
leave default settings, press Next
leave default settings, press Next
Drag and drop Receive shape to orchestration
Select message SQLPoll for it and connect with newly created Port
Start Wizard to create output port
Name it FileSndPort leave default settings except selecting that you will use the port to send information
Name it FileSndPort leave default settings except selecting that you will use the port to send information
Create new Send shape , assign SQLPoll message to it and connect it with FileSndPort
Your orchestration should look like this
Now from project properties change Application name to TestSQLWCF and sign the DLL
Deploy the project.
To create WCF-SQL port
In BizTalk Administration Console
In BizTalk Administration Console
Select newly created Application TestSQLWCF
Create Receive Port and Location
in location set WCF-SQL adapter configure it with the same settings you setup in
wizard while creating Generated Item
Then create Send Port and assign it to a folder on your disk
Bind logical ports and physical ports as shown on the picture
Start Application
in output directory check for new files
in output directory check for new files
Comments
I'm trying to setup database polling with BizTalk 2010 and SQL Server 2008 R2.
When I follow the steps in your post I'm getting stuck at the following step.
Check that you connected to DB
select Service(inbound operations)
in Select a category choose TypedPolling than Add dbo.PollData
At this stage, when I select Service(inbound operations) and category I don't see dbo.PollData anywhere. There are three items in the Available categories and operations list.
* Polling
* Typed Polling
* Notification
Any ideas?
Thanks...
You should select Typed Polling
Use TypedPolling
I followed these steps but at the end the output record is still empty
I did not understand what I must do
any ideas ?
thanks
just check if your proc returns records. You can also use SQL profiler to monitor returned records
Thanks
Regards,
Rajasekhar.R
I am happy that my articles can help you.
Thanks