Transactional Adapter (BizTalk Server Sample)


The Transactional Adapter sample demonstrates how to create and use an explicit Microsoft Distributed Transaction Coordinator (MSDTC) transaction against a database during processing of a BizTalk Server message.

This sample contains a receive adapter which runs a SQL statement at a user-specified interval to obtain data from a SQL Server database using an MSDTC transaction. The data is then submitted to the BizTalk Server MessageBox database in the form of a message under the context of the same transaction.

The corresponding send adapter runs a user-specified SQL stored procedure using input from a BizTalk message in the context of a transaction. It uses specific data from that message to locate and delete the corresponding message from the Message Box database under that same transaction.

This sample has two projects in its solution. The first is the administrative project (Admin) which is used prior to runtime to allow a user to configure the receive locations and send ports that use this adapter. The second is the runtime project (Runtime) that runs when the send and receive adapters are executing.

The sample is located in the following SDK location:

<Samples Path>\Samples\AdaptersDevelopment\TransactionalAdapter. The administrative configuration project is located in the \Admin folder, while the runtime project exists in the \Runtime folder.

The following table shows the files in this sample and describes their purpose.

Admin Project FilenameAdmin Project File Description
TransactionalAdmin.csprojAdapter administrative project file used for pre-runtime configuration
TransactionalReceiveHandler.xsdXSD for receive handler properties
TransactionalReceiveLocation.xsdXSD for receive location properties
TransactionalTransmitLocation.xsdXSD for transmit location properties
TransactionalTransmitHandler.xsdXSD for transmit handler properties
TransactionalAdapterManagement.csAdapter configuration management. Contains GetConfigSchema which is invoked by the BizTalk Adapter Framework to return an XSD configuration schema for each of the (four) possible configuration types it supports.
Runtime Project FilenameRuntime Project File Description
Transactional.csprojAdapter runtime project file
TransactionalAsyncBatch.csAsynchronous implementation of the send part of the adapter
TransactionalDeleteBatch.csDeletes a batch of messages and votes to commit or abort a transaction
TransactionalProperties.csExtracts and sets configuration properties
TransactionalReceiver.csCreates and manages receive endpoints
TransactionalReceiverEndpoint.csActual listening or polling for each receive location
TransactionalTransmitter.csAccepts a batch of messages from the Messaging Engine to be transmitted

This sample is meant as a framework for you to use in creating custom send and receive adapters using explicit transactions.

System_CAPS_ICON_important.jpg Important

If the BizTalk installation is 64-bit or the location of installation is modified, OutboundAssemblyPath, InboundAssemblyPath, AdapterMgmtAssemblyPath would need to be changed accordingly.

To Create a Strong Name Key for the Transactional Adapter sample

  1. Start Visual Studio Command Prompt.

  2. At the command prompt, type the following and then press enter:

    cd \Program Files\Microsoft BizTalk Server <version>\SDK\Samples\AdaptersDevelopment\TransactionalAdapter\Runtime  
  3. At the command prompt, type the following and then press enter:

    sn –k TransactionalAdapter.snk  
  4. At the command prompt, type exit, and then press enter to close the command prompt window.

To Build the Transactional Adapter Solution

  1. Click Start, point to All Programs, point to Accessories, and then click Windows Explorer.

  2. Browse to drive:\Program Files\Microsoft BizTalk Server 2xxxSDK\Samples\AdaptersDevelopment\TransactionalAdapter, and double-click TransactionalAdapter.sln to open this solution in Visual Studio.

  3. To build both of the Transactional Adapter projects (Admin and Runtime) in Solution Explorer, right-click Solution TransactionalAdapter, and then click Rebuild.

To register the Transactional Adapter

  1. In Windows Explorer, navigate to drive:\Program Files\Microsoft BizTalk Server 2xxxSDK\Samples\AdaptersDevelopment\TransactionalAdapter\Admin.

  2. To add the transactional adapter data to the registry, double-click TransactionalAdmin.reg.

    System_CAPS_ICON_note.jpg Note

    TransactionalAdmin.reg includes hard-coded paths to C:\Program Files\Microsoft BizTalk Server\. If you did not install BizTalk Server in the default location or if you upgraded your BizTalk Server installation from a previous version, you must modify the file TransactionalAdmin.reg with the appropriate paths. Update the paths associated with the "InboundAssemblyPath", "OutboundAssemblyPath", and "AdapterMgmtAssemblyPath" values to point to the correct location of the specified files.

    System_CAPS_ICON_important.jpg Important

    If you install BizTalk on a 64 bit machine, change all instances of the HKEY_CLASSES_ROOT\CLSID\ registry entry to HKEY_CLASSES_ROOT\Wow6432Node\CLSID\ in the TransactionalAdmin.reg registry file.

  3. In the Registry Editor dialog box, click Yes to add the sample adapter to the registry, and then click OK.

  4. To close Windows Explorer, click File, and then click Close.

Add the Transactional Adapter to BizTalk Server

  1. Click the Start menu, select All Programs, select Microsoft BizTalk Server 20xx, and then select BizTalk Server Administration.

  2. In the BizTalk Server Administration, expand the BizTalk Server Administration tree, expand the BizTalk Group tree, and then expand the Platform Settings tree.

  3. Right-click Adapters, click New, and then click Adapter.

  4. In the Adapter Properties dialog box, do the following.

    Use thisTo do this
    NameType TransactionalAdapter.
    AdapterSelect Txn from the drop-down list. This entry appears as a result of running the TransactionalAdmin.reg file previously.
    DescriptionType Sample Transactional Adapter.
  5. Click OK. The adapter now appears in the list of adapters in the right window of the BizTalk Administration console.

Create a Receive Port and Location that uses the Adapter

  1. Expand the BizTalk Group[server name] node in BizTalk Server Administration, expand Applications node, expand BizTalk Application 1 node.

  2. Right-click Receive Ports, and then click New, select One-Way Receive Port.

  3. For Name, enter TxnReceivePort1, and then click OK.

  4. Right-click the Receive Locations node, click New, and then select One-Way Receive Location.

  5. In theSelect a Receive Port dialog box, select TxnReceivePort1, and then click OK.

  6. In the Receive Location Properties dialog box, under the General tab, enter TxnReceiveLocation1 for Name. Ensure the Receive Port label displays TxnReceivePort1.

  7. In theType dropdown list box, in the Transport frame, select TransactionalAdapter.

  8. In the Receive****Pipeline box, ensure PassThruReceive is selected. Leave the rest of the properties with their default settings.

  9. Click the Configure button next to the Type drop down box. This displays a dialog specific to this adapter. Specify the following as you see appropriate, then click OK.

    Connection StringThe SQL database connection string used to connect and authenticate with the Northwind database. We will later run a SQL script that will use this database.
    Command TextThe SQL statements that are executed against the Northwind database to obtain data to put into a BizTalk Message.
    CookieComprises part of the URI so enter a unique value, such as the name of the receive location, for instance: TxnReceiveLocation1.
    Polling Interval UnitThe number of units of time measure for the polling of the data. Set this to seconds.
    Polling IntervalThe unit of time measure for the polling of the data. Set this to 15 seconds.
  10. Click OK to close the Configure dialog box, then OK again to close the Receive Location Properties dialog box to return to the BizTalk Server Administration.

Create a Send Port and Send Handler that use the Adapter

  1. With the BizTalk Application 1 node still expanded, right-click Send Ports, and then click New, and select Static One-Way Send Port.

  2. In the Name field, enter TxnSendPort1.

  3. In the Transport frame, in the Type drop-down list, selectTransactionalAdapter.

  4. In the Send Pipeline box, ensure PassThruTransmit is selected.

  5. Click the Configure button next to the transport drop-down list.In the dialog that appears specify the following as you see appropriate, then click OK.

    CookieComprises part of the URI - Enter a unique value here such as the name of the receive location, for instance: TxnSendPort1.
    Connection StringThe SQL database connection string used to connect and authenticate with the Northwind database. It will most likely be the same one used to configure the TxnReceiveLocation1 receive location.
    Stored ProcedureThe stored procedure name that gets executed to poll the database - sp_txnProc. The body of the BizTalk message is given to that stored procedure as a string parameter called @Data. For example, the user will in this case later configure the stored procedure with the name sp_txnProc. The runtime of the adapter would execute the equivalent of this call to the database.

    exec sp_txnProc @Data = “the contents of the BizTalk message”
  6. In the left navigation pane, click Filter.

  7. In the filter expression editor, enter the following expression to set up a subscription for this send port to receive any messages received by the TxnReceivePort1 receive port.

    Enter these values:BTS.ReceivePortName== TxnReceivePort1

    1. (property)  BTS.ReceivePortName

    2. (operator)  ==

    3. (value)  TxnReceivePort1

  8. Use the default values for the remainder of the adapter properties and select OK.

To run the Transactional Adapter sample

  1. Click Start, point to All Programs, point to Microsoft SQL Server 2008 R2, select SQL Server Management Studio.

  2. In the Connect to Server dialog box, make sure Server Type is set to Database Engine, and enter credentials to authenticate to the database server, then select Connect.

  3. Select the New Query toolbar button and paste the following into the new query window to insert a test table, test data, and a test stored procedure into the Northwind database. Select the Execute toolbar button.

    use [Northwind]  
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[scratch]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)  
    drop table [dbo].[scratch]  
    CREATE TABLE [dbo].[scratch] (  
         [id] [int] IDENTITY (1, 1) NOT NULL ,  
         [msg] [nvarchar] (4000) NOT NULL   
    ) ON [PRIMARY]  
    GRANT SELECT , UPDATE , INSERT ON [dbo].[scratch] TO [public]  
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_txnProc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)  
    drop procedure [dbo].[sp_txnProc]  
    CREATE PROCEDURE [dbo].[sp_txnProc] @Data nvarchar (4000)  
    INSERT scratch ( msg ) values ( @Data )  
    GRANT EXECUTE ON [dbo].[sp_txnProc] TO [public]  
  4. In BizTalk Server Administration, expand the Send Ports node, select the TxnSendPort1 send port, and select Start.

  5. In BizTalk Server Administration, expand the ReceiveLocations node, select the TxnRecieveLocation1 receive location, and then select Enable.

  6. Once the receive location is enabled, it will automatically poll the database at the designated intervals for data.

IBTTransmitterBatch Interface (COM)

IBTTransportProxy Interface (COM)

Adapter Samples - Development
Registering an Adapter

Community Additions