WCF LOB Adapter SDK and BizTalk Adapter Pack

Inserting parent child records with Identity column using WCF-SQL Adapter in one transaction

SandeepP — Tue, 13 Oct 2009 03:58:00 GMT

The following blog post by Atin nicely explains how to go about doing this

http://blogs.msdn.com/biztalkcpr/archive/2009/10/05/inserting-parent-child-records-with-identity-column-using-wcf-sql-adapter-in-one-transaction.aspx

Using the WCF SQL Adapter to submit messages to SSB queues from BizTalk

SandeepP — Wed, 07 Oct 2009 07:11:00 GMT

This post is a follow-up to http://blogs.msdn.com/adapters/archive/2008/06/30/using-the-wcf-sql-adapter-to-read-messages-from-ssb-queues-and-submit-them-to-biztalk.aspx and explains how to push a message using the WCF SQL Adapter from BizTalk to a SQL Service Broker (SSB) queue.

Scenario

1. An XML message is dropped to a file share

2. This XML message is made available to the WCF SQL Adapter by using the File Adapter

3. The WCF SQL Adapter then pushes this XML message to a preconfigured SSB queue by invoking a Stored Procedure

Create the database artifacts required for the SSB conversation

1. A message type, which denotes the format of the message in the queue

2. A contract, which denotes the conversation between a sender and a receiver and also includes the type of message flowing between them

3. The Initiator & Target queues, where messages are stored

4. The Initiator & Target services, which utilize the above queues

USE master;

ALTER DATABASE <your db name here>

SET ENABLE_BROKER;

USE <your db name here>;

CREATE MESSAGE TYPE

[//SqlAdapterSSBSample/RequestMessage]

VALIDATION = WELL_FORMED_XML;

CREATE CONTRACT [//SqlAdapterSSBSample/SampleContract]

([//SqlAdapterSSBSample/RequestMessage]

SENT BY INITIATOR

);

CREATE QUEUE InitiatorQueue1DB;

CREATE SERVICE

[//SqlAdapterSSBSample/InitiatorService]

ON QUEUE InitiatorQueue1DB;

CREATE QUEUE TargetQueue1DB;

CREATE SERVICE

[//SqlAdapterSSBSample/TargetService]

ON QUEUE TargetQueue1DB

([//SqlAdapterSSBSample/SampleContract]);

5. A stored procedure, say InitiatorSP, that will take the message as an argument and push it to the SSB queue. Let’s use the name RequestMsg for the argument.

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dbo].[InitiatorSP]

@RequestMsg xml

BEGIN

DECLARE @DlgHandle UNIQUEIDENTIFIER;

BEGIN DIALOG @DlgHandle

FROM SERVICE

[//SqlAdapterSSBSample/InitiatorService]

TO SERVICE

N'//SqlAdapterSSBSample/TargetService'

ON CONTRACT

[//SqlAdapterSSBSample/SampleContract]

WITH ENCRYPTION = OFF;

SEND ON CONVERSATION @DlgHandle

MESSAGE TYPE

[//SqlAdapterSSBSample/RequestMessage]

(@RequestMsg);

END

Create the BizTalk artifacts

1. Start the BizTalk Server 2009 Administration Console

2. Create a new BizTalk application, say SSBSendApplication

3. Create a new Receive Port, say FileReceivePort and add a new Receive Location, say FileReceive

a. Set the Type to File and configure the Receive Folder to point to a local share, say c:\in

4. Create a new Static One-way Send Port, say SqlSendPort

a. In the General tab,

i. Set the Type to WCF-SQL

ii. Click Configure and set the properties as follows

1. In the General tab, set

a. Address – the format is “mssql://<servername>/<instancename>/<databasename>”. For example, on my machine (using the default instance of SQL server), mssql://localhost//SSBTestDb (where SSBTestDb is the name of my database)

b. Action – the format is “TypedProcedure/<schemaname>/<storedprocedurename>”. For example, in my case, it is TypedProcedure/dbo/InitiatorSP

2. In the Messages tab, select Template and fill in the XML box with the following

<InitiatorSP xmlns="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo">

<RequestMsg>

<bts-msg-body xmlns="http://www.microsoft.com/schemas/bts2007" encoding="string"/>

</RequestMsg>

</InitiatorSP>

*Note that this approach requires that the xml encoding is string.

iii. Leave the other properties as is

b. In the Filters tab, add a filter BTS.ReceivePortName == FileReceivePort

5. Create a new Static One-way Send Port, say FileSendPort

a. In the General tab, set the Type to File and configure the Receive Folder to point to a local share, say c:\out

b. In the Filters tab, add a filter BTS.SPName == SqlSendPort

6. At this point the configuration of BizTalk application is completed, so start the application.

Send the message to SSB queue

1. Drop a request file to the c:\in share (one that file receive port is using). Note that this exact message will show up in the SSB queue. Here’s a sample message

<RequestMessage>Hello World</RequestMessage>

Consume the message from SSB queue

1. You can now consume the message from the SSB queue. On running the below query, you will see the above message.

DECLARE @DlgHandle UNIQUEIDENTIFIER;

DECLARE @RecvMsg XML;

RECEIVE TOP (1)

@DlgHandle=conversation_handle,

@RecvMsg = CAST(message_body as XML)

FROM TargetQueue1DB;

IF NOT (@DlgHandle IS NULL)

BEGIN

END CONVERSATION @DlgHandle;

SELECT @RecvMsg AS ReceivedMessage;

END

Introducing the Migration Tool for Microsoft BizTalk Adapters for Enterprise Applications

Nitin Mehrotra_MSFT — Tue, 16 Jun 2009 18:03:00 GMT

For customers currently using the Microsoft BizTalk Adapters for Enterprise Applications (aka the LOB adapters) and willing to migrate their existing projects to the WCF-based LOB adapters, here's a tool that will help you migrate your projects to work with BizTalk Adapter Pack 2.0.

Before we start talking about the tool and the adapter projects it can be used against, let us set the ground by laying the rules for the naming convention that we will be using here:

Microsoft BizTalk Adapters for Enterprise Applications adapters are referred to as non-WCF LOB adapters.
Adapters part of BizTalk Adapter Pack 2.0 are referred to as WCF-based LOB adapters.

What does the tool do?

The migration tool accepts BizTalk project/Solution files containing schemas generated by non-WCF LOB adapters and generates corresponding new BizTalk project/solution files having schemas corresponding to the WCF-based LOB adapters, with the maps and orchestrations modified accordingly. This tool helps you to migrate the projects for the following adapters:

BizTalk ODBC Adapter for Oracle Database
BizTalk Adapter v2.0 for mySAP Business Suite
SQL adapter

Where to get the tool from?

You can download the tool from http://go.microsoft.com/fwlink/?LinkID=153328. The tool also includes a migration guide that describes:

How to run the tool
What steps to follow after the tool has finished migrating the project
Limitations of the migration tool.

Why does the OracleEBS adapter say “Could not retrieve User ID, Responsibility ID, and Application ID”?

MSDNArchive — Wed, 03 Jun 2009 20:59:00 GMT

Sometimes, the Oracle EBS adapter throws this error while invoking artifacts like Concurrent Programs, Interface Tables, Interface Views and a few PL-SQL APIs.

The message in the event viewer looks like the following (snipped some portions to improve readability):

Event Type: Warning
Event Source: BizTalk Server 2009
...

Description:
The adapter failed to transmit message going to send port "<your_send_port_name>" with URL "oracleebs://<YourURI>". It will be retransmitted after the retry interval specified for this Send Port.

Details:"Microsoft.ServiceModel.Channels.Common.ConnectionException: Could not retrieve User ID, Responsibility ID, and Application ID. These values are required to set the application context. Make sure that you have specified correct values in the binding properties or the message context properties for setting the application context.

---> Microsoft.ServiceModel.Channels.Common.MetadataException:

---> Oracle.DataAccess.Client.OracleException ORA-01403:

....

at Microsoft.Adapters.OracleEBS.OracleEBSConnection.InitializeApps(...)
at Microsoft.Adapters.OracleEBS.OracleEBSConnection.OpenConnection(...)

The Oracle adapter throws this error when it is not able to set app context using the Responsibility-Name (or responsibility-key), application-short-name and apps-user provided by the user. You can read up on app-context in earlier blogs posts (http://blogs.msdn.com/adapters/archive/2008/10/14/oracle-ebs-adapter-application-context.aspx and http://blogs.msdn.com/adapters/archive/2008/10/17/precedence-while-setting-application-context-in-oracle-ebs-adapter.aspx).

One of the commonly asked questions is "How do I determine the correct responsibility name to be used?".

Here is a tool that you can use to determine the responsibility that you need to set app context. The tool will prompt will prompt you for relevant details like credentials, application short name etc.

Usage: CheckAppContext [/Responsibility:<Responsibility>]

- If the responsibility name is not provided, the tool lists the valid responsibilities that could be used for the apps user <AppsUserName> in the application <AppsShortName>.

- If the responsibility name is provided, the tool checks whether the provided combination of <AppsShortName> and <Responsibilty> can be used to set app context for the Apps User <AppsUserName>.

NOTE: The responsibility should be enclosed in quotes if it has spaces else quotes are not required .

Using Oracle Database or Oracle EBS adapter for Oracle AQ (Advanced Queuing)

manasg — Mon, 25 May 2009 14:31:00 GMT

Here is a simple way to get started on Oracle AQ (Advanced Queuing) using the Oracle Database and/or Oracle E-Business Suite adapters. Here is what I did, right from defining my queue - one step at a time.

Step 1 - Define the queue payload type
CREATE TYPE AQ_MESSAGE_TYPE AS OBJECT (ID VARCHAR2(30), INFO VARCHAR2(200));

Step 2 - Define the queue table
EXEC DBMS_AQADM.CREATE_QUEUE_TABLE(QUEUE_TABLE => 'INFO', QUEUE_PAYLOAD_TYPE => 'AQ_MESSAGE_TYPE');

Step 3 - Create the queue
EXEC DBMS_AQADM.CREATE_QUEUE(QUEUE_NAME => 'AQ_MESSAGE_QUEUE', QUEUE_TABLE => 'INFO');

Step 4 - Start the queue
EXEC DBMS_AQADM.START_QUEUE(QUEUE_NAME => 'AQ_MESSAGE_QUEUE');

Step 5 - Create a function to enqueue
CREATE OR REPLACE FUNCTION ENQUEUE_AQ_MESSAGE_QUEUE(PAYLOAD IN AQ_MESSAGE_TYPE) RETURN RAW
AS
PROPERTIES DBMS_AQ.MESSAGE_PROPERTIES_T;
MSGID RAW(16);
OPTIONS DBMS_AQ.ENQUEUE_OPTIONS_T;
BEGIN
DBMS_AQ.ENQUEUE('AQ_MESSAGE_QUEUE',
    OPTIONS,
    PROPERTIES,
    PAYLOAD,
    MSGID);

RETURN MSGID;
END;

Step 6 - Create a function to dequeue
CREATE OR REPLACE FUNCTION DEQUEUE_AQ_MESSAGE_QUEUE(PAYLOAD OUT AQ_MESSAGE_TYPE) RETURN RAW
AS
PROPERTIES DBMS_AQ.MESSAGE_PROPERTIES_T;
OPTIONS DBMS_AQ.DEQUEUE_OPTIONS_T;
MSGID RAW(16);
NOTHING_TO_DEQUEUE EXCEPTION;
PRAGMA
EXCEPTION_INIT (NOTHING_TO_DEQUEUE, -25228);
BEGIN
OPTIONS.WAIT := DBMS_AQ.NO_WAIT;
DBMS_AQ.DEQUEUE('AQ_MESSAGE_QUEUE',
    OPTIONS,
    PROPERTIES,
    PAYLOAD,
    MSGID);
RETURN MSGID;
EXCEPTION
WHEN NOTHING_TO_DEQUEUE
  THEN RETURN NULL;
END;

A word on dequeue and enqueue functions - these are simplistic in this example. You might want to modify them to take more inputs (for example - more message properties or enqueue/dequeue properties as input). The dequeue function returns immediately if a message is not available for dequeue and returns null in that case. You could tweak that too.

Step 7 - Call the functions using the adapter!
It is easy calling these from the adapter now. The reason why the DBMS_AQ.ENQUEUE and DBMS_AQ.DEQUEUE cannot be called directly from the adapter is that they use complex data types that are not supported by ODP.NET. One can now even call the dequeue function in a polling context, and get messages delivered to them as they are pushed into the queue! The links to portions of the Oracle Database adapter help that will help you do this are:

Developing BizTalk applications http://msdn.microsoft.com/en-us/library/dd788030.aspx
Support for Oracle User Defined Types http://msdn.microsoft.com/en-us/library/dd788045.aspx

Thanks to Mustansir for suggesting this blog idea!

WCF Siebel Adapter - Query fails because of misconfigured fields

SandeepP — Mon, 11 May 2009 14:24:00 GMT

Siebel adapter uses the COM interface to invoke calls on Siebel. For business component query, Siebel supports two ways of retrieving the field values for records. One can either use GetFieldValue to retrieve one field at a time or the other option is to use GetMultipleFieldValues to retrieve values for a bunch of fields in one go. The latter is better from a performance standpoint and is what the adapter uses. The problem with this approach though is that even if one of the fields, whose values are being retrieved, is mis-configured, the entire query call fails.

In development environments, it can happen that some of the customizations have issues, resulting in query failure as described above. The first step towards diagnosing the problem is identification of those problematic fields. The error message doesn’t always help identify the fields. When in such situation, one can use the attached Siebel COM sample code to narrow down the fields. You will need to add a reference to sstchca.dll (present in the Siebel install folder, in the bin sub-directory).

Once you have narrowed down the fields, you can either fix the issue with the field or if it’s not of interest, mark them as inactive on Siebel backend. You can then query using the Siebel adapter without any issues. Alternately, you can also use the QueryFields (optional) argument passed to the query operation to retrieve only the fields of interest.

BizTalk Adapter Pack 2.0 is now available

Vivek Krishna — Mon, 04 May 2009 08:40:00 GMT

The BizTalk Adapter Pack 2.0 was released to manufacturing and is now generally available .

For BizTalk 2009 users (except branch edition), the Adapter Pack 2.0 license comes free and can be downloaded from the volume licensing site.

Biztalk 2006 R2 users can get the Biztalk Adapter Pack if they have Software Assurance for Biztalk.

Others will have to buy it seperately.

The prequsite for this is the WCF LOB Adapter SDK SP2. A 120 day evaluation version of the Adapter Pack 2.0 is also now available . The below table summarizes all the links you need to get started with the Adapter Pack 2.0 .

Item	Link
Product
WCF LOB Adapter SDK SP2(pre-req for BAP 2.0)	http://go.microsoft.com/fwlink/?LinkId=147367
Adapter Pack 2.0 120 day Evaluation Version	http://www.microsoft.com/downloads/details.aspx?FamilyID=76736ba7-3c05-4436-9353-1c33f9005194&displaylang=en
Adapter Pack on volume licensing site (requires access)	http://www.microsoftvolumelicensing.com/userights/ProductPage.aspx?pid=281
MSDN Subscribers (requires access)	http://msdn.microsoft.com/subscriptions/downloads under BizTalk Server Accelerators and Adapters
SQL Adapter SKU Download(For BizTalk branch edition customers)	http://go.microsoft.com/fwlink/?LinkId=147379
Documentation and Samples
MSDN Location of Adapter Pack 2.0 docs	http://go.microsoft.com/fwlink/?LinkId=149364
Download location for individual CHMs in Adapter Pack 2.0	http://go.microsoft.com/fwlink/?LinkId=147355
Download location for Adapter Pack 2.0 Installation Guide	http://go.microsoft.com/fwlink/?LinkId=147364
Download location for SQL Adapter Installation Guide and CHM	http://go.microsoft.com/fwlink/?LinkId=147377
Download location for all the samples for Adapter Pack 2.0	http://go.microsoft.com/fwlink/?LinkID=145144

The Forum to ask any questions or look for answers is here . This version also includes the Customer Experience Improvement Program. Do turn it on so that we get some valuable feedback that can be used for future improvements.

Connection failure when using the WCF Siebel Adapter

SandeepP — Fri, 17 Apr 2009 07:07:00 GMT

I have seen quite a few instances where people run into issues while using the WCF Siebel adapter because of

a. Siebel Web Client is not installed correctly on the machine

b. Or the URI passed to the adapter, that eventually gets transformed to the connection string passed to Siebel, is incorrect

In order to eliminate the above 2 as possible causes, you can try out the following sample code that will use the Siebel COM interface to directly talk with Siebel (adapter is not involved). Please make sure you don’t see any error messages.

/// Application that will issue Login-Logoff request to Siebel

///

/// To run this program,

/// - Set ConnectString, Username and Password. ConnectString is of the format

/// siebel://host/EnterpriseServer/AppObjMgr/SiebelServer

/// (SiebelServer is required in case of Siebel 7.5)

/// For an example, please see the code below.

/// - Add a reference to sstchca.dll (found in siebel client install folder)

/// - Compile and run the program

/// - If the login fails, you will see an error message

using System;

using System.Runtime.InteropServices;

using SiebelBusObjectInterfaces;

namespace Sample

{

class Program

{

const string ConnectString = "host=\"siebel://adapsblsrvr77:2321/ent77/SSEObjMgr_enu";

const string Username = "abc";

const string Password = "def";

static void Main(string[] args)

{

SiebelDataControl sdc = null;

bool loggedIn = false;

try

{

/// Create a connection

sdc = new SiebelDataControl();

sdc.EnableExceptions(1);

sdc.Login(ConnectString, Username, Password);

loggedIn = true;

}

catch (COMException ex)

{

Console.WriteLine("ERROR: " + ex.ToString());

}

finally

{

if (sdc != null)

{

if (loggedIn)

{

sdc.Logoff();

}

Marshal.ReleaseComObject(sdc);

sdc = null;

}

Console.WriteLine("Press any key to terminate...");

Console.ReadLine();

}

Things to consider when writing WCF LOB Adapters for consumption through BizTalk

SandeepP — Thu, 12 Mar 2009 08:22:00 GMT

If you are writing a WCF LOB adapter that can be consumed through BizTalk, you need to be aware of certain issues that can manifest because of the way BizTalk interacts with WCF adapters. Some of these can have performance and scalability impact and hence you should consider them when designing/configuring the adapter.

Processing in IConnection/IConnectionFactory instance – When using SSO, for each message, BizTalk will end up creating a new IConnectionFactory and a new IConnection instance. If your adapter is doing a lot of processing in either of those instances, it can cause significant performance impact. So you will need to think of alternatives – possibly doing the processing upfront and caching.
Caching of LOB artifacts on IConnection instance – Typically you will have some LOB artifacts that will comprise the context associated with an IConnection instance. If your adapter is using WCF LOB Adapter SDK’s connection pooling, in the non SSO scenario, the IConnection instances will be closed only when the idle connection timeout expires. So you need to think about freeing up the LOB resources that comprise your connection context. If you make idle connection timeout too small, it would impact performance since LOB connection creation will incur an overhead and if you keep it at a large value then you will be holding on to LOB artifacts for a longer time than you really need to, possible scalability/performance impact.
Throughput stall – You can find more details about the issue on http://blogs.msdn.com/adapters/archive/2008/08/18/throughput-stalls-when-using-adapters-developed-against-the-wcf-lob-adapter-sdk-in-biztalk.aspx. Note that if you don’t limit the number of concurrent connections through your adapter, you won’t run into this problem. The problem happens the contention between Open and Send/Execute for thread pool threads.
Writing/promoting properties to BizTalk message context – When the adapter receives a WCF message from BizTalk, it will have all the properties comprising the BizTalk message context. For messages that originate from the adapter, if you want to have properties either written/promoted to BizTalk message context, you can look at http://technet.microsoft.com/en-us/library/bb246105.aspx on how to go about doing that.
Transaction overhead – BizTalk will by default start a transaction and send the message/process the response in that transaction scope. This can cause a significant overhead particularly if say the LOB doesn’t support transactions. In BizTalk 2009, there is an option on the Send port that lets you configure whether transactions should be enabled or not.

Connect to WCF LOB Adapter hosted as WCF service using ClientCredentials

SandeepP — Tue, 10 Mar 2009 13:00:00 GMT

On the website http://msdn.microsoft.com/en-us/library/dd451003.aspx, we have a step by step guide to publish the Siebel adapter as a WCF Service in IIS and then have MOSS consume the data using that service. In this approach, credentials are pass by MOSS to the adapter using custom HTTP headers.

If instead you want to consume this service from a .net application or any client that wants to pass the credentials using the ClientCredentials object, this is how you will go about doing it. Follow the steps on http://msdn.microsoft.com/en-us/library/dd450994.aspx that gives detailed instructions about publishing the WCF service. There are 2 differences though

1. In the “Configure service and endpoint behaviors” page, for “AuthenticationType” you will need to select “ClientCredentialUsernamePassword” (as opposed to “HTTPUsernamePassword” that the tutorial specifies)

2. In the “Configure the service endpoint binding and address” page, click on the BindingConfiguration (the … following BasicHttpBindingElement) to customize the binding. Set Security Mode to “TransportWithMessageCredential”

Follow the rest of the instructions for publishing the service. You can now connect to the service using a .net proxy application and pass the credentials using the ClientCredentials object.