1. Create database and tables 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, Amo
Last month I had a post were described how to debatch XML message in orchestration using XPath. http://biztalk.kirakosyan.com/2010/02/debatching-records-from-wcf-sql-using.html How I wrote it is not the best solution , in case if you will have hundreds of records the debatching using XPath will consume too much memory and processor. In this post I am going to show how to debatch using pipeline. The sample will fetch multiple records from MS SQL database and after debatching put them to folder. How to create simple WCF-SQL ports and SQL polling you can read in my old post http://biztalk.kirakosyan.com/2010/02/how-to-pull-database-using-wcf-sql.html I will use the same procedure and table to generate Schemas and PortTypes In generated schema change envelop type to yes Next click on TypePolling which is the root for schema In properties find Body XPath and edit it as in picture is Then change the last node to have Max Occurs = 1 Now we should remove hidden stones :-) When you
I will continue on my sample which demonstrated records polling from SQL using WCF-SQL adapter How I wrote in my last post it is not the best solution , it will be much slower then disassembling data in pipeline (which I will post in 2-3 days) So what I want to do. First I will count the number of records retrieved from SQL then using LOOP shape I will iterate through records and send each to a file adapter If you did not read my two last posts , read them and create BizTalk project , install SP and create table in MSSQL , create application and ports in BizTalk I will continue on the same orchestration Add new variable in orchestration and set Identifier : RecordsCount Type : System.Int32 After Send shape insert Expression shape Set expression : RecordsCount = (System.Int32)(xpath("count(/*[local-name()='TypedPolling' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/TypedPolling/ID1']/*[local-name()='TypedPollingResultSet0' and namesp
Comments