Introduction

BizTalk Server Adapter Pack 2010 is new enhanced version of the previous Adapter Pack 2.0. This pack has been updated to support .NET 4.0 Framework and Visual Studio 2010. Adapter Pack provides connectivity to latest versions of SAP, Oracle E-Business Suite, Siebel and SQL and Oracle Databases. It is no longer a separate download as previous packs. It is present on install medium (DVD/ISO) of BizTalk Server 2010 and important component for BizTalk AppFabric Connect Feature (i.e. LOB capabilities in .NET applications).

Background

As BizTalk Server evolved the number of adapters grew the Enterprise Adapters became first class citizen of BizTalk adapter‟s collection. Out of the box BizTalk offers a lot of adapters that support numerous databases, protocols and applications. The adapter pack extended the number of adapters to provide more integration capabilities. Interoperability to SAP has been supported by Microsoft since 2000 with connectivity to SAP R/3. Adapters for SAP were based initially on the BizTalk Server Adapter Framework and later on the Adapter Pack 1.0 release which makes use of the Windows Communication Framework (WCF). Besides SAP Microsoft in August 2005 acquired eight .NET based application adapters from iWay. These adapters allowed BizTalk Server customers to easily connect to third-party applications like Amdocs, JD Edwards, Oracle Corp., PeopleSoft, Siebel Systems Inc. and TIBCO Software Inc. This purchase of the iWay adapters took place before the major release of BizTalk Server (2006), SQL Server (2005) and Visual Studio (2005). These adapters, which were based on the BizTalk Server Adapter Framework (.NET Framework 2.0), were bundled into the “Microsoft BizTalk Adapters for Enterprise Applications” offering which targeted at BizTalk 2006 and later BizTalk Server 2006 R2.

Microsoft continued to work on improvements of the LOB adapters and in March 2008 the BizTalk Adapter Pack 1.0 was made available. This pack contained three adapters for Siebel, SAP and Oracle Database based on WCF LOB Adapter SDK. It offered the possibility to connect to a variety of clients (such as Microsoft Office, BizTalk Server, and custom .NET applications). The SDK inherently relies on the core WCF concepts, principles, and classes implemented in the .NET Framework 3.0 and later. It provides for a simplified programming model on top of WCF Channel Model to enable adapter developers in creating service-oriented interfaces to existing systems and applications. Microsoft delivered this Pack to enable a flexible integration and application infrastructure connecting LOB systems with a unified open approach on the technology of the WCF programming model.

With BizTalk 2009 Microsoft continued their commitment to simplify interoperability. In Server Adapter Pack 2.0, the BizTalk Server Adapter Pack 1.0 was extended with Oracle E-Business Adapter and SQL Server Adapter. This SQL Server adapter replaces the native SQL adapter offered out-of-the-box from BizTalk Server and offers many more features, to name a few:

• Support for new data types in SQL2005 and SQL2008 (notably FILESTREAM, varbinary (max))
• Insert/Update/Delete/Select operations on tables and views
• Invoke (SQL and CLR) Stored procedures, scalar valued functions , table value functions
• Supports execution of generic T-SQL statements, via the ExecuteReader(), ExecuteNonQuery() and ExecuteScalar() operations
• Configurable polling – polling statement, polling interval, receive multiple result sets
• Support for x86 and x64 platforms

Latest version of BizTalk 2010 contains same adapters as adapter pack 2.0 and includes support for new versions of the following line-of-business applications and databases: SAP 7, Oracle EBS 12.1 and SQL Server 2008 R2.  The Adapter Pack also provides support for Visual Studio 2010 and .NET Framework 4. The adapter pack was no longer a separate component, but became part of the BizTalk Server 2010 Edition supporting capability to offer LOB capabilities in .NET applications (i.e. AppFabric Connect).

Architecture

Adapters in the Adapter Pack 2010 are like preceding packs 1.0 and 2.0 based on WCF LOB Adapter SDK, which consists of a run-time, a collection of APIs, and design-time tools for creating adapters that expose data and operations from line-of-business systems. Adapters manage messages between the adapter consumer and the line-of-business system and can consist of meta-data, data, or other information. The figure shows the internal architecture and main components of WCF LOB Adapter SDK (excerpt of BizTalk Adapter Pack 2010 Poster).

Picture above shows two types of developers:

• Solution developer consuming an adapter.
• Adapter developer implementing a custom adapter.

Adapter development will not be discussed in this article and focus is on the solution developer working with the Adapter Pack 2010. Channel implementation is an important part of architecture. An adapter built using the WCF LOB Adapter SDK is basically a transport channel (System.ServiceModel.Channels.IServiceListner). It is surfaced to the consumer as a WCF binding, where the binding is used to create the channel stack. This binding can be considered a peer to other predefined WCF bindings such as BasicHttpBinding, WsHttpBinding, and NetTcpBinding, and can be set via app.config or in code by the client application when calling a service. This binding contains an ordered set of binding elements, with the adapter being the key binding element that derives from the T:System.ServiceModel.Channels.TransportBindingElement class. In an outbound scenario, the WCF LOB Adapter SDK runtime uses a channel factory to create the adapter (that is, the transport channel). With an inbound scenario, the WCF LOB Adapter SDK runtime makes use of channel listeners for incoming channels in a service application. Both run-time as well as design-time messages pass through this component.

Other important parts of WCF-based architecture for an adapter consumer are:

• The Connection Uri Builder allows adapter consumers to programmatically build connection URIs without specific knowledge of syntax.
• The Metadata Exchanger is responsible to handles the client‟s metadata messages including Adapter SDK‟s Browse / Search.
• The WSDL Builder provides automatic WSDL generation from the WCF LOB Adapter SDK's internal metadata object model (it can be overridden for scenarios that require custom WSDL generation).
•  The Metadata Browse/Search allows for browsing and searching all LOB metadata.
• The Metadata management is responsible for the object-oriented representation of the caching of the metadata for the target system. Metadata can be held in a common cache accessible across all credentials, or it can be cached per credential basis.
• The Connection Pool Management is responsible for the lifetime management of the connections of an adapter. It internally keeps a pool of the connections ready for use. This connection pool is credential and URI-based. The credential contains a user name and password that define the security context the connection runs under.

And important tool once the WCF LOB Adapter SDK is installed in your development environment for solution developers is the Adapter Metadata Utility (through Add Adapter Service Reference Plug-in, Consume Adapter Service Add-in) available within Visual Studio 2010.

Adapter Pack Landscape

The goal of the WCF LOB Adapter SDK is to facilitate uniform development of reusable metadata-oriented WCF-based adapters that enable enterprise applications, databases and messaging platforms to integrate with each other. The picture below will show this ( another excerpt of BizTalk Adapter Pack 2010 Poster).

Installation and Configuration

To install the BizTalk Adapter Pack 2010 you will need an environment or a virtual machine with for instance the following components installed:

• Microsoft .NET Framework 3.5 SP1 and Microsoft .NET Framework 4 (.NET Framework 4 is essential to use BizTalk Adapter Pack with BizTalk Server 2010. However, you can use .NET Framework 3.5 SP1 and .NET Framework 4 to write .NET applications (channel/proxy) for connecting using adapters.)
• Microsoft Visual Studio 2010.
• The Microsoft Windows Communication Foundation (WCF) Line of Business (LOB) Adapter SDK (WCF LOB Adapter SDK)2010 (present on installation media of BizTalk Server 2010).
• Respective enterprise application clients and associated software.

With installation you are guided through a wizard installing WCF-LOB SDK first, then adapter pack itself and subsequently in x64 environment the Adapter Pack for x64. Finally option to install the BizTalk Adapter Pack for Enterprise Applications (non-WCF based adapters). See this post on guidance for installation.

After installation of the adapter pack you do not see a new adapter show up under the Adapters folder or in the drop down for Add new Adapter. The adapter pack installs new custom bindings. The BizTalk "adapter" for instance for the Oracle database shows up in BizTalk as the OracleDBBinding. The standard bindings are wsHttpBinding, netTcpBinding, etc. OracleDbBinding and SqlBinding are themselves custom bindings.

Sample Implementation

With BizTalk Adapter Pack 2010 there is a plethora of scenario's possible integrating with LOB. One possibility is to perform operations on SQL Server through messaging by using the WCF-Custom adapter, you must perform a set of design-time and run-time tasks using Visual Studio and BizTalk Server Administration console respectively. The following high level tasks are required to create a message based solution:

• Create BizTalk project, and generate schema.
• Create messages instances.
• Build and deploy the BizTalk project.
• Configure the application.
• Start the application.

Create BizTalk project, and generate schema

1. Open Visual Studio and create a new BizTalk project.
2. First, we need the XML schemas, which represent our data source. By installing the WCF LOB Adapter SDK, you will have a metadata browser built into Visual Studio. NET at your disposal. You can access this from a BizTalk project by right-clicking the project, choosing Add then Generated Items and selecting Consume Adapter Service.
3. This action opens a new window that provides us the interface to connect to, browse, and select database objects for service interaction. The very first thing we need to do here is choose the sqlBinding as the service binding, and then configure a connection string. The simplest working connection string consists of an Initial Catalog value for the database, and a valid Server name entry. Note that the adapter now uses a connection string in the form of a URI instead of the traditional Data Source=;Initial_Catalog=;User_ Id=;Password= style. 
4. Once a satisfactory connection string is defined, the Connect button can be clicked to establish an active connection to the target database. If the connection is successful, one see the category browser with a set of database object categories. 
5. On the root "/" character a range of untyped generic functions are displayed, which can be exploited.
6. Click on tables and you will see a number of Tables, select Products. 
7. Click the Delete, Insert, Select and Update statement in available categories and operations.
8. Click the Add button to add the operations.
9. After clicking the Ok button at the bottom, schemas (and a single binding file) is added to our associated BizTalk project in Visual Studio.NET. 
10. The binding is required later on, when solution is deployed and ports have to be configured for SQL Server (i.e. Send Port). This binding has to be edited for each operation (Select, Update, Insert, and Delete), because there is no corresponding logical ports as this a complete message based solution (if you keep original binding file and import in than you will run into error described in this post Why does the Adapter say ‘Action is not understood’ even though I am using the binding file generated by the ‘Consume Adapter Service’ wizard?). Choose to create four new binding files, each a copy of original, but all with a single operation in BtsActionMapping.

Create messages instances

1. Next step is to generate messages from generated schema, which can be routed from Receive location in Receive Port to a send port with WCF-Custom Adapter. Generating the first message is easy, as you can right click on the TableOperation.dbo.Products.xsd and select Generate Instance and an instance of top element is created (e.g. Select). 
2. By opening up the TableOperation.dbo.Products.xsd in file-editor you can move the next operation to top, so you can then in Visual Studio generate the next instance for an operation or you can use XML-Editor inside Visual Studio (closing schema, right click and select open with… and then select XML Editor) to move elements around. In end you will have four xml instance for TableOperations, save to file as xml (type all files, encoding UTF-8!).

Build and deploy the BizTalk project

1. Only schemas are required for a messaging only scenario with WCF-SQL Adapter to perform operations on SQL Server.
2. Assign a string name to project.
3. Assign application name to project.
4. Build and deploy the solution.

Configure the application

1. After solution has been deployed it is time to configure the ports. FILE ports are easy and focus here will be configuring the WCF-Custom Send Ports. Through BizTalk Management Console one can browse to BTS.SQL.Table.Operations application and right click the application and select Import Bindings … and navigate to file called WcfSendPort_SqlAdapterBinding_Custom_Select.bindinginfo.xml, select and click Open.
2. You will notice that a Send Port will be created automatically. You can repeat the import for binding: WcfSendPort_SqlAdapterBinding_Custom_Delete.bindinginfo.xml, WcfSendPort_SqlAdapterBinding_Custom_Insert.bindinginfo.xml, and WcfSendPort_SqlAdapterBinding_Custom_Update.bindinginfo.xml.
3. Next steps are setting filters on WCF-Send Port and File Send Port. Configuration is as follows:

PortName	Type	Filter
RPTableOperationIn	FILE
SPTableOperationOut	FILE	BTS.MessageType == http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Products#UpdateResponse Or BTS.MessageType == http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Products#SelectResponse Or BTS.MessageType == http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Products#InsertResponse Or BTS.MessageType == http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Products#DeleteResponse
WcfSendPort_SqlAdapterBinding_TableOp_dbo_Products_Delete	WCF-Custom	BTS.MessageType == http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Products#Delete
WcfSendPort_SqlAdapterBinding_TableOp_dbo_Products_Insert	WCF-Custom	BTS.MessageType == http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Products#Insert
WcfSendPort_SqlAdapterBinding_TableOp_dbo_Products_Select	WCF-Custom	BTS.MessageType == http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Products#Select
WcfSendPort_SqlAdapterBinding_TableOp_dbo_Products_Update	WCF-Custom	BTS.MessageType == http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Products#Update

Start the application

In the end your messaging solutions can be depicted as below. After configuration you can start BizTalk application. Schema below gives an overview what will happen once drop message in folder (e.g. In folder).

Code for this solution can be found on code gallery: BizTalk Table Operations - Messaging.

Forum

Questions on issues see the BizTalk Server Adapters and Adapter Pack forum.

Community Resources

• Using FOR XML queries with the WCF-SQL Adapter
• BizTalk AppFabric Connect: An Introduction
• Work with Appfabric Connect
• BizTalk + WF/WCF, Better Together
• Expose BizTalk Applications on the Cloud Using AppFabric Connect for Services

Technet Wiki

• BizTalk Adapter Pack 2010 - WCF OracleDb Adapter
• BizTalk Adapter Pack 2010 – WCF SQL Adapter
• BizTalk Server 2010 Adapter Pack - SQL Azure
• BizTalk Adapter Pack 2010 - WCF SAP Adapter
• BizTalk Adapter Pack 2010 - WCF Oracle E-Business Adapter
• BizTalk Adapter Pack 2010 - WCF based Siebel Adapter

See Also

• BizTalk Adapter Pack
• BizTalk Adapters
• AppFabric Connect
• Documentation for BizTalk Server Adapter Pack 2010
• BizTalk Adapter Pack 2010 Poster
• BizTalk Adapter Pack 2010 Interactive Poster
• BizTalk Adapter Pack 2010 Samples
• Microsoft BizTalk 2010: Line of Business Systems Integration book
• Installation and licensing

Other languages

This article is also available in the following languages

• Norwegian: BizTalk Server Adapter Pack 2010 (nb-NO)