Testing Ordered Delivery Using a SQL Send Port (WCF)

This section describes the steps followed to validate ordered delivery behavior using a WCF-SQL Send Port in BizTalk Server.

Prerequisites

• BizTalk Server environment

• SQL Server with required permissions

• Visual Studio with BizTalk project template

Implementation Steps

1. Create Stored Procedure
   A SQL stored procedure named SP_OrderedDeliveryTest was created to support the ordered delivery test.

2. Generate Stored Procedure Schema
   Using Visual Studio, a schema was generated from the stored procedure by selecting:
   Add Generated Items → Consume Adapter Service (WCF-SQL Adapter).

3. Create Input Schema
   An additional input schema was created to represent the inbound message structure.

4. Develop Mapping
   A map was created to transform the input schema to the stored procedure schema.

   • The map includes an integer field used to control the number of seconds the stored procedure remains idle (sleep), simulating processing delay.

5. Deploy and Configure BizTalk Artifacts

   • The BizTalk project was compiled and deployed.

   • A Receive Port was configured to receive input messages from a file location.

   • A Send Port was configured using the binding file generated during schema creation.

6. Prepare Test Messages
   Ten instances of the input schema were created.

   • The AppCode value and numeric fields were modified to ensure each message was unique.

7. Execute Ordered Delivery Test
   Input files were dropped sequentially into the file location.

   • The drop sequence was recorded (e.g., 5, 7, 9, 1, 3, 4, 2).

8. Verify Results
   The AppCode values were mapped to the stored procedure and written to the database table.

   • Records were inserted in the same order as the files were dropped.

   • BizTalk waited for the SQL send operation to complete before sending the next request, confirming ordered delivery behavior.

Notes

• Binding files are included with the BizTalk project for reference and reuse.

Top of Form

Bottom of Form

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

SP_OrderdDeliveryTest SP

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

USE [ICC]

GO

/****** Object: StoredProcedure [dbo].[SP_OrderedDeliveryTest] ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

ALTER PROCEDURE [dbo].[SP_OrderedDeliveryTest]

-- Add the parameters for the stored procedure here

@ITMI_INTEGRATION_NAME varchar(30),

@ITMI_OrderDeliveryTestCol INT

AS

BEGIN

DECLARE @DelayTime INT

SET @DelayTime = @ITMI_OrderDeliveryTestCol * 5

DECLARE @strDelayTime varchar(8)

SET @strDelayTime = '00:00:'+CONVERT(VARCHAR,@DelayTime)

WAITFOR DELAY @strDelayTime

INSERT INTO ITM_INTEGRATIONS (ITMI_INTEGRATION_NAME,

ITM_INTEGRATIONS.ITMI_OrderDeliveryTestCol,ITMI_CREATED_DATE) VALUES

(@ITMI_INTEGRATION_NAME,@ITMI_OrderDeliveryTestCol,SYSDATETIME())

END

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

SOME QURIES

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

USE [ICC]

GO

DECLARE @return_value int

EXEC @return_value = [dbo].[SP_OrderedDeliveryTest]

@ITMI_INTEGRATION_NAME = N'OAI',

@ITMI_OrderDeliveryTestCol = 1

--@DelayTime = N'05'

SELECT 'Return Value' = @return_value

GO

select * from dbo.ITM_INTEGRATIONS

--delete from dbo.ITM_INTEGRATIONS