I recently (10 October 2008) posted a solution to this problem at the

 

It has saved my day again.

BizTalk WSS Adapter is returning empty strings when using the XPath values as referenced in the documentation at Windows SharePoint Services Adapter Expressions

The document recommends use of syntax as in "PurchaseOrder2 %XPATH=//pons:PurchaseOrder/pons:PurchaseOrderID%.xml". I think the document should clarify that the XPath syntax is valid where element names are "qualified" :)

For example, if you follow the walkthrough modules for the WSS adapter the following expression will work:

"PurchaseOrder2 %XPATH=//pons:PurchaseOrder/PurchaseOrderID%.xml"

Hope this helps

There are two Oracle adapters:

1. LOB Oracle DB adapter released as part of R1 - This adapter only works with Tables and has very limited stored procedures execution capability, i.e. stored procedures returning or accepting row sets do not generate valid schemas.
2. The BizTalk Adapter Pack based on WCF LOB Adapter SDK using WCF is supposed to overcome those limitations.

Note: Both adapters use ODBC data source to connect to Oracle database.

Installing Oracle WCF Adapter from the BizTalk Adapter Pack

Please check the BizTalk Adapter Pack to ensure that you have a compatible version of database and Oracle client installed.

Configuration details for the Oracle WCF Adapter

Sujan's article on Configuring WCF-Custom Adapter for Oracle databases describes the installation process with links to all required components.

Troubleshooting the Oracle WCF Adapter connectivity

After the installation, the key points to get the WCF Oracle adapter working are:

1. I have used the http://msdn.microsoft.com/en-us/library/cc185273.aspx as guidance to resolve:

"Connecting to the system LOB failed. Could not load file or assembly 'Oracle.DataAccess' Version=2.102.2.20, Culture=neutral, PublicKeyToken=<token>' or one of its dependencies. The system cannot find the file specified."

 By default, Oracle Policy files ensure the use of latest versions of the Oracle.DataAccess.dll.

I had Policy files 1.102 (v1.102.4.0), 10.1(v1.102.2.20) and 10.2 (v1.102.2.20) installed in GAC. Removing the Oracle Policy registrations from GAC resolved error.

2. Manually register the Oracle.DataAccess.dll. As it is already registered - unregister it first and register it using the gacutil. This step ensures you can connect successfully to Oracle database.
3. If you get the following error: "Connecting to the LOB system has failed. ORA-12154: TNS:could not resolve the connect identifier specified." Make sure that the ODBC data source name is same as the alias Oracle expects, otherwise it will result in this error and will not connect to Oracle via WCF LOB Oracle adapter.
4. The username and password are both case sensitive for Oracle DB. Although from ODBC you can use lowercase username to connect to Oracle via ODBC test and Toad, in WCF LOB Oracle adapter, the same was required in uppercase - I have no explanation for this
5. If you get error about adapter being incompatible with the Oracle client, remove the Oracle client and install the versions as recommended in BizTalk Adapter Pack.

Using the Oracle Package

The following shows a sample Oracle package that was used to generate the schemas and bindings using the Consume Adapter Service option.

    TYPE TechDocsDocAdd IS RECORD (

        DOC_NBR char,

        DOC_REV char,

        TITLE_LINE char     

    );  

   

    TYPE TechDocsDocAddCursor IS REF CURSOR RETURN TechDocsDocAdd; 

   

   PROCEDURE NType_TechDocs_DocAdd(

      i_targetdate   IN       DATE,

      o_resultset    OUT      TechDocsDocAddCursor

    );

 

Note, that where table and package operations are selected at once, the table operation is rarely included in the action mapping in the generated port binding definitions. This leads to “message routing failure” suggesting that the schema for the root element cannot be found.  If this happens, check that you have all the operations listed in the port action mapping in the binding file.

To overcome the above issue, I have used two separate ports one for table related operations and another for Packages. This basically means that I have to generate two sets of metadata via the Consume Adapter service wizard. This is not my preferred option, but it works 100%.

 

 

So in my next attempt I started from W2K3 with SQL server base VPC. At this point the BI components were installed as part of SQL Server 2005 installation and therefore VS2005 was already installed. I proceeded in the following order:

Every thing worked from the word go :)

 

Note: One of my colleague, Chris Hewitt, suggested "the VPC should be a domain controller – if you want to be able to use WebSSO, Profile Imports etc." Keep this in mind if you plan to use these features.