The new "WCF LOB Adapter SDK"-based SQL Adapter - Features

  • Article

The current release of the Adapter Pack contains the SAP, Siebel and Oracle adapters (written using the WCF LOB Adapter SDK).

For the next release of the Adapter Pack, one of the adapters which we're adding is a SQL Adapter. Some of the features which we're addressing are:

  • SQL 2005 and SQL 2008 datatypes (XML, VARCHAR(MAX), VARBINARY(MAX), Date, Time, DateTime2, DateTimeOffset, Geometry, Geography, HierarchyId)
  • User Defined Types (UDTs - these can be defined in .NET assemblies)
  • Table Valued Parameters in Functions and Stored Procedures
  • Native 64 bit support (the adapter can run in a 64 bit BizTalk host)
  • Enterprise SSO (courtesy of the WCF adapter)
  • User-defined schemas (schemas other than dbo)
  • Specifying the transaction IsolationLevel to use during polling
  • An ExecuteReader() operation which can be used to execute arbitrary SQL statements at runtime
  • Executing any stored procedure - FOR XML is no longer required.
  • Supporting multiple resultsets in the response - either from stored procedure execution, or from arbitrary SQL statements, and for both - outbound as well as polling scenarios
  • Streamed operations for reading/writing from/to VARCHAR(MAX) and VARBINARY(MAX) columns
  • Dynamic Ports (i.e., specifying the endpoint information within an orchestration)
  • Ability to perform multiple operations (INSERT, UPDATE, DELETE, Execute Stored Procedure) in the same transaction.

Are there any other features which you would like to see in a new SQL Adapter? Please do let us know.

We're looking for TAP customers to try out and validate the features in the new adapter. An alpha release of the adapter (having all the above features) is also currently available, so if you're interested, please do let us know and join the TAP program.

Comments

  • Anonymous
    February 04, 2008
    I know in the past there have been issues with dynamic db connections not being supported. It would be nice to be able to set the url within the orchestration.We are still on biztalk 2006, so not sure if this has been addressed in R2 or not.
  • Anonymous
    February 04, 2008
    If possible, could you expand upon what type of UDT support?  With the Oracle LOB Adapter, this was limited to non-hierarchical typed cursors OUT and typed non-collection parameters IN.  It would be useful to have strongly typed hierarchical collections IN/OUT.  For example, a purchase order containing 1 or more detail lines.
  • Anonymous
    February 04, 2008
    these features sound great. i assume you going to be doing send & receive sides with one-way and sollict-response options?i'd be very interested in testing these new features, how do i become part of the TAP program?
  • Anonymous
    February 04, 2008
    Hi Patrick.The new SQL adapter will support dynamic ports - you will be able to set the SQL connection URI within the orchestration.
  • Anonymous
    April 02, 2008
    How do I get an Alpha relaese of the adapter or join the TAP program?
  • Anonymous
    April 08, 2008
    Hi GacSpk. If you leave your email address, we'll get in touch with you. Don't worry about spam - I'll remove the comment once I have your address :)