Logo
programming4us
programming4us
programming4us
programming4us
Windows XP
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Windows Phone
 
 
Windows Server

BizTalk 2010 : ASDK SQL adapter examples (part 1) - TypedPolling and debatching

12/2/2012 11:38:19 AM
In this section we will cover some of the frequently used operations provided by the ASDK SQL adapter. We prepared a few easy to follow examples that focus on the details of the adapter operations and not on building the entire BizTalk application itself or the details of the SQL Server objects used.

For these examples we have a scenario where sales order details are kept inside a few different tables in a SQL Server database.

The three main tables used in the examples are SalesOrderHeader, SalesOrderStatus, and SalesOrderDetails.

The SalesOrderHeader table contains the base information about the order including when it is due, shipped, the Account number, CustomerID, SalesPersonID, and so on:

The SalesOrderStatus table tracks the status of the Order from a systems integration perspective. Since we do not want BizTalk to process orders multiple times, we need to manage the records that have been consumed. This table provides us the ability to mark records as being processed by specifying a GUID in the ProcessingIndicator field. When it is set to NULL it identifies that the rows need to be processed by BizTalk Server.

The OrderStatus column is used to identify different stages of the sales order processing, and SalesOrderID is the link to the tables actually holding sales order information:

The SalesOrderDetail table provides us with some supplementary information about the order including the UnitPrice, Discount, CarrierTracking Number, and so on:

The best practices for the old SQL adapter still apply to the new ASDK SQL adapter when it comes to designing the receive side T-SQL code to avoid deadlocks. The main difference is the new adapter does not require FOR XML at the end of stored procedures, and the schemas it generates are easier to configure for debatching. 

SQL Server deadlocks can occur or duplicate data retrieved if the T-SQL code being called by the adapter cannot be changed to follow best practices and there are multiple BizTalk Server host instances mapped to the host that runs the polling receive location. In these cases, clustering the host can be a way of ensuring that the solution is highly available while still avoiding deadlocks.



Example 1—TypedPolling and debatching

This first example will demonstrate how the TypedPolling operation of the ASDK SQL adapter can be used to poll the database for sales orders that haven't yet been processed by the BizTalk Server.

The TypedPolling operation is the most frequent operation used when BizTalk Server solutions need to poll SQL Server for data. The schema for the polling results is strongly typed and can be easily used in maps or have its elements promoted for content-based routing.

We have created a stored procedure that returns the first 100 rows that have not yet been processed by BizTalk Server. We will describe the store procedure in more details later.

Since we already have a stored procedure, as provided in our database script for this example, our next step is to generate the typed schema representing the resultset generated by the procedure. In the Consume Adapter Service Wizard we set sqlBinding as the binding and click Configure to open the Configure Adapter window to specify the URI and Binding Properties. On the URI Properties tab we set the properties to connect to our sample AdventureWorksCh2 database. Note, if your SQL server is local, you can specify "localhost" for the Server property:

The value of InboundId is used by the adapter when building the namespace for the TypedPolling inbound operation schemas and also in the binding file that it generates containing the BizTalk port configuration that will be used later. This value ensures the connection URI is unique across multiple receive locations that poll from the same database. It is very important that we use the same InboundId when configuring our Receive Location as we used in the Consume Adapter Service wizard. Otherwise, we can expect a BizTalk runtime error.

The Binding Properties tab is where we set the adapter properties that support the typed polling operation. Here are some of the significant properties that we need to set:

  1. The inboundOperationType needs to be set to for this scenario. Since we are interested in receiving a strongly typed result set from SQL Server, we need to populate this property with a value of TypedPolling.

  2. The PolledDataAvailableStatement is used at runtime to check whether any data is available to be polled. If it returns a positive value in its first column then the PollingStatement statementis executed. The benefit approach to this is that we can provide a lightweight query to determine if data exists as opposed to a more complex polling query that may consume more resources when executing. In this example we configure the PolledDataAvailableStatement with a query that checks for sales orders not yet processed:

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    SELECT COUNT(*) FROM Sales.SalesOrderStatus WITH(UPDLOCK ROWLOCK READPAST )
    WHERE OrderStatus = ‘Pending’AND ProcessingIndicator IS NULL
    
  3. You may have noticed we are setting the isolation level at the beginning of the statement and providing SQL hints on the select query. This is to avoid deadlocks in multi-server environments.

  4. The PollingStatement setting is the statement that will be executed every time the PolledDataAvailableStatement returns a positive value. The same PollingStatement used during development must be used when configuring receive location. This is required since the setting is used by the adapter at the runtime to create a message matching the schema generated by the Consume Adapter Service wizard. For the purpose of this example we are going to set our PollingStatement to a stored procedure that returns the first 100 sales orders that haven't yet been processed and marks them as processed:

    exec dbo.usp_GetSalesOrdersToProcess
    

    You can also separate multiple T-SQL statements with a semi-colon. For example, if you have a stored procedure to get the sales orders and another to mark them as processed, the PollingStatement can be:

    exec dbo.usp_GetSalesOrdersToProcess; exec dbo.usp_UpdateSalesOrdersToProcess


    Our stored procedure updates the ProcessingIndicator column with a new GUID value for the first 100 rows where the ProcessingIndicator is NULL. Then it returns the data in the SalesOrderHeader table that relate to those 100 rows. We have chosen to update the records that we want to process before selecting them. By using this approach, we can ensure that we are controlling the amount of records that we want to return to BizTalk per polling event. Here is the stored procedure:

    CREATE PROCEDURE [dbo].[usp_GetSalesOrdersToProcess]
    AS
    SET nocounton
    set xact_abort on
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    declare @procIndicator uniqueidentifier
    set @procIndicator =NEWID();
    UPDATE Sales.SalesOrderStatus WITH (ROWLOCK)
    SET ProcessingIndicator = @procIndicator,
    OrderStatus ='Processing'
    FROM (
    SELECT TOP 100 SalesOrderID
    FROM Sales.SalesOrderStatus WITH(UPDLOCK ROWLOCK READPAST)
    WHERE ProcessingIndicator IS NULL AND OrderStatus ='Pending' ORDER BY SalesOrderID)
    AS t1
    WHERE (Sales.SalesOrderStatus.SalesOrderID = t1.SalesOrderID)
    SELECT TOP 100 soh.[SalesOrderID],[RevisionNumber]
    ,[OrderDate],[DueDate]
    ,[ShipDate],[Status]
    ,[SalesOrderNumber],[PurchaseOrderNumber]
    ,[AccountNumber],[CustomerID]
    ,[ContactID],[BillToAddressID]
    ,[ShipToAddressID],[ShipMethodID]
    ,[CreditCardApprovalCode],[SubTotal]
    ,[TaxAmt],[Freight]
    ,[TotalDue],[Comment]
    FROM Sales.SalesOrderHeader soh WITH(UPDLOCKROWLOCKREADPAST)
    INNERJOIN Sales.SalesOrderStatus sos ON soh.SalesOrderID = sos.SalesOrderID
    WHERE sos.ProcessingIndicator = @procIndicator
    
    
    					  

    The PollingIntervalInSeconds specifies how often BizTalk Server will run the SQL statement set on the PolledDataAvailableStatement property. The polling interval must be set carefully to both avoid unnecessary polling when the data doesn't change often but to also poll at a frequency high enough to pick up the changes on a timely basis. We set it to 60 seconds.

  5. The last property to cover for our example is PollWhileDataFound. Our goal is to get batches of 100 sales orders from SQL Server. Setting a limit on the number of rows returned by the polling statement in conjunction with the PollWhileDataFound property (when set to true) is a good practice to ensure multiple smaller messages are created by the adapter. This technique is also important because more than one message can be processed in parallel by BizTalk Server instead of creating one large message. When set to true, the PollWhileDataFound property instructs the adapter to ignore the PollingInterval property and call the polling statement continuously. For our current example, we have set this property to False so that we can clearly understand BizTalk's polling behavior. If we processed all data continually it would reduce our visibility into BizTalk's polling cycles.

Here is the Binding Properties tab configured for our example:

A property that deserves special attention in polling scenarios is ReceiveTimeout. This property indicates the amount of time that the adapter will wait for an inbound message. The Microsoft documentation recommends setting this property to the maximum allowed value of 24.20:31:23.6470000 (24 days). Setting a large value like this does not impact the performance of the adapter. We do need to ensure that this value exceeds our Polling interval to ensure that we have connectivity between our BizTalk Server(s) and SQL Server before reaching this timeout threshold.


After we have set the binding properties we need to connect to our SQL Server, select Service (Inbound operations), select the root / category, and add the TypedPolling operation:

Once we have configured our Consume Adapter Service wizard as illustrated in the preceding image, we can now click the OK button. The Consume Adapter Service wizard then adds a schema to the BizTalk Server project (the schema is called TypedPolling.SalesOrders.xsd in this example) and also creates a binding file containing configuration details for the physical receive port and location.

The schema that will be used in our Typed Polling scenario includes an array of the result sets of the stored procedure. The schema is easy to configure if we want it further debatched by BizTalk Server. Our stored procedure was created to return 100 sales orders at the most with each poll. We can modify the schema created by the adapter so that the XmlDisassembler pipeline will split each of these repeating nodes into individual messages. The first step is to set the <Schema> node's Envelope property to Yes:

Then, set the Body Xpath value of the TypedPolling node to the first TypedPollingResultSet0. This will tell the XmlDisassembler that anything below this record should be disassembled into a separate message:

The previous two steps are all that need to be set during development for the TypedPolling schema to configure its messages to be debatched.

After deploying the solution, the binding file that was created by the Consume Adapter Service wizard can be imported into the BizTalk Administration Console to create the receive port and receive location with all the URI and binding settings we created during development. Included in the sample code is a sample binding file for the entire solution.

This receive location will then poll for sales orders that have not yet been processed, pass them to BizTalk Server as a batch, and each message containing the sales order information is debatched. The following XML shows one of the debatched messages:

<TypedPollingResultSet0 xmlns="http://schemas.microsoft.com/Sql/2008/05/TypedPolling/SalesOrders">
<SalesOrderID>43669</SalesOrderID>
<RevisionNumber>10</RevisionNumber>
<OrderDate>2001-07-01T00:00:00Z</OrderDate>
<DueDate>2001-07-13T00:00:00Z</DueDate>
<ShipDate>2001-07-08T00:00:00Z</ShipDate>
<Status>5</Status>
<SalesOrderNumber>SO43669</SalesOrderNumber>
<PurchaseOrderNumber>PO14123169936</PurchaseOrderNumber>
<AccountNumber>10-4020-000578</AccountNumber>
<CustomerID>578</CustomerID>
<ContactID>295</ContactID>
<BillToAddressID>895</BillToAddressID>
<ShipToAddressID>895</ShipToAddressID>
<ShipMethodID>5</ShipMethodID>
<CreditCardApprovalCode>25877Vi80261</CreditCardApprovalCode>
<SubTotal>881.4687</SubTotal>
<TaxAmt>70.5175</TaxAmt>
<Freight>22.0367</Freight>
<TotalDue>974.0229</TotalDue>
</TypedPollingResultSet0>


					  

This example demonstrated how the ASDK SQL Adapter and BizTalk Server's out of the box functionality can be used to poll and extract data from SQL Server.

Other -----------------
- Microsoft Dynamics AX 2009 : Integration with Microsoft Office - Sending email using Outlook
- Microsoft Dynamics AX 2009 : Integration with Microsoft Office - Exporting data to Microsoft Project
- Microsoft Content Management Server : The ASP.NET Stager Application (part 3) - Staging Attachments
- Microsoft Content Management Server : The ASP.NET Stager Application (part 2) - Staging Channels and Postings
- Microsoft Content Management Server : The ASP.NET Stager Application (part 1) - The DotNetSiteStager Project, Recording Messages to a Log File
- Microsoft Content Management Server : Staging Static Pages - Site Stager in Brief
- BizTalk 2010 : WCF LOB SQL Adapter - Consuming ASDK SQL Adapter in Visual Studio (part 2)
- BizTalk 2010 : WCF LOB SQL Adapter - Consuming ASDK SQL Adapter in Visual Studio (part 1)
- Windows Server 2008 Server Core : Renaming a File with the Ren and Rename Commands, Sorting File Content with the Sort Utility
- Windows Server 2008 Server Core : Moving Files and Renaming Files and Directories with the Move Command, Recovering Lost Files with the Recover Utility
 
 
Video tutorials
- How To Install Windows 8 On VMware Workstation 9

- How To Install Windows 8

- How To Install Windows Server 2012

- How To Disable Windows 8 Metro UI

- How To Change Account Picture In Windows 8

- How To Unlock Administrator Account in Windows 8

- How To Restart, Log Off And Shutdown Windows 8

- How To Login To Skype Using A Microsoft Account

- How To Enable Aero Glass Effect In Windows 8

- How To Disable Windows Update in Windows 8

- How To Disable Windows 8 Metro UI

- How To Add Widgets To Windows 8 Lock Screen
programming4us programming4us
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 windows Phone 7 windows Phone 8
programming4us programming4us
 
Popular keywords
HOW TO Swimlane in Visio Visio sort key Pen and Touch Creating groups in Windows Server Raid in Windows Server Exchange 2010 maintenance Exchange server mail enabled groups Debugging Tools Collaborating
programming4us programming4us
 
programming4us
Girls
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Windows Phone