Wade Wegner

Having used the “Microsoft BizTalk Adapters for Host Systems” to connect to a DB2 back-end, I thought I was ready for any adapter challenge.  Turns out that using the “Microsoft BizTalk Adapters for Enterprise Applications” to connect to Oracle was a little trickier than I thought it would be.

To start, I couldn’t figure out where to find the Oracle adapter!  If you read the BizTalk 2006 Pricing and Licensing page (http://www.microsoft.com/biztalk/howtobuy/default.mspx) you’ll see that “ODBC Adapter for Oracle Database” is included in the license!  To me that insinuates no additional installation or download is required, but that’s not the case.  There are actually two prerequisites to using the Oracle adapter.

First, you need the Oracle client installed on your machine.  Yes, in retrospect, this seems perfectly obvious (but, in hindsight, what isn’t?).  Second, you need to acquire a copy of “Microsoft BizTalk Adapters for Enterprise Applications” and install the “Microsoft BizTalk Adapter for Oracle(r) Database”.

With regards to the Oracle client, there is a good article on MSDN (also found in the help documentation) that will walk you through the installation and configuration of the Oracle client (http://msdn2.microsoft.com/en-us/library/aa547634.aspx).  I recommend using this tutorial.  The installation for the “Microsoft BizTalk Adapters for Enterprise Applications” is straightforward, and shouldn’t give you any problems.

Once the two of these applications are installed (and you’ve registered the appropriate TNS names for Oracle connectivity), you must create an ODBC connection.  To do this, do the following:

1. Administrative Tools -> Data Sources (ODBC)
2. Click the System DSN tab, followed by Add
3. Select “Oracle in OraHome92″ (yours may be slightly different)
4. You’ll now have to configure your ODBC connection.  Make sure to specify the Data Source Name, the TNS Service Name, and the User ID.  When you click “Test Connection” you will be prompted for the password.  Make sure the test passes.
5. Click “OK”, and “OK” to exit.

Now that the ODBC connection is created, we have to setup the Oracle adapter in the BizTalk Administrative Console.  Under the BizTalk Group, expand “Platform Settings” and “Adapters”.  Notice that there isn’t an Oracle adapter.  You’ll have to right-click on “Adapters” and choose “New” –> “Adapter…”.  From here you can choose the “Oracle(r) Database” adapter, and specify a name.  I like “OracleDB”.  The default Host will be added automatically for both Send and Receive.

Now that all the prerequisites are out of the way, we have to go about first creating a receive port/location or a send port.  Now, in my experience, this is somewhat backwards.  Typically I first choose “Add Generated Item”, “Add Adapter Metadata”, and then select the adapter I want to use.  With Oracle, you must first create your Receive Port/Location or Send Port, and then use it to define your schema.

Here’s the process I use to create a Recieve Port/Location

1. Open the “BizTalk Administration Console” (I’m not a big fan of doing this in Visual Studio 2005)
2. Create a new Receive Port (let’s do One-Way for simplicity), and call it “ReceiveDataFromOracleDB”
3. Create a new One-way Receive Location on the “ReceiveDataFromOracleDB” port.  Specify a decent name — “ReceiveDataFromOracleDB-ORA” isn’t the best, but will do
4. Select the “OracleDB” adapter you defined earlier, and click “Configure”.  Specify the following values
   1. Password
   2. PATH — with a default installation of Oracle, this would be “C:Oracleora92bin” (yours may be slightly different)
   3. Service name — this is the ODBC connection you created
   4. User name
5. At this point I’d recommend not specifying anything else, and clicking okay.  This will save the settings, and allow you to continue forward.  Having clicked okay, go ahead and immediately click “Configure” again to return to the properties.
6. Click “Managing Events”.  This will call the Oracle Adapter Wizard, and confirm that you have correctly configured everything.  If you are unable to browse the Oracle database, then you have made a mistake somewhere along the way.  Click OK or Cancel to exit.

At this point, just leave your Receive Location alone.  The next step will be to use the Receive Port/Location to generate a schema of an Oracle table or stored procedure.

Enter into Visual Studio 2005, and load your BizTalk project (I won’t explain how to do this — if you don’t already have one, then create one).  A a new item, choose “Add Generated Items …”, then select “Add Adapter Metadata”.  This will invoke the “Add Adapter Wizard”.  Select “Oracle(r) Database” from the list of registered adapters, then choose your receive location in the “Port:” dropdown list.  Click Next.  You will now have the ability to browse the Oracle database, and choose either your stored procedure or table.  For simplicity sake, we’ll choose a table.  The adapter will generate the schemas required to actually retrieve data from the Oracle table.  Build your project and deploy.

Once deployed, you can perform the final steps to this process.  Go back to your Receive Location, and click the Configure button.  Once again, click “Managing Events”.  This time, choose the same table that you specified in the adapter wizard.  Click OK.  Now, create a Send Port that uses the File adapter, and subscribe to the Receive Port (this is, in my opinion, the easiest way to test).

If everything has been done correctly, you should see XML files dump into your folder.  If they don’t, then look into the application log and try to figure out what the problem is.  Sadly, that’s beyond the scope of this post.

I hope this has helped.  Please let me know if I’ve missed anything.

Best of luck!

I’ve been working on a project where we have two independent BizTalk 2006 clusters — one at the headquarters, and one (actually, a few) at some a remote office.  In order to send messages back and forth, we are using HTTP as the transport protocol.

For example, we first query an AS400/DB2 database (via the HOST adapters for BizTalk 2006) through a receive port/location.  This is picked up by a subscribing send port, which posts it via HTTP to the remote office.  That remote office has an receive port/location configured to listen on HTTP, via the BTSHTTPReceive.dll ISAPI extension.  This is then picked up by a send port that then inserts the data into an MSSQL database.

Having set this up a few times now, I’ve found that I keep making the same mistakes.  Consequently, here’s my short-list of items to watch for when setting up HTTP for both send/receive in BizTalk 2006:

• Make sure your application pool is configured to run under the context of your BizTalk use account
• Confirm that your virtual directory has Scripts and Executables execute permissions
• Copy the file “BTSHTTPReceive.dll” into your virtual directory from the folder “C:Program FilesMicrosoft BizTalk Server 2006HttpReceive”
• Make sure to configure a “Web Service Extension” in IIS for the “BTSHTTPReceive.dll” file
• Remove anonymous access to your virtual directory, and use either Integrated or Basic authentication (preferrably Integrated)
• Make sure your send port utilizes the same security context, so that it can post to the site
• Confirm that your BizTalkUser account belongs to the BizTalk Server Administrators group

For your send port, configure your destination with a querystring value, like this: http://localhost/transport/BTSHTTPReceive.dll?MessageType.  I like to append the message type, but that’s up to you.  For your receive location, configure your virtual directory plus ISAPI extension to something like: /transport/BTSHTTPReceive.dll?MessageType.

That’s all I can think of off the top of my head.

Best of luck!

If you get the following error with an HTTP send port in BizTalk 2006, you may have an authentication issue:

A message send to adapter “HTTP” on send port “SendPort1″ with URI http://localhost/vd/BTSHTTPReceive.dll?QueryString is suspended.

Error details: This request requires buffering data to succeed.

Check the following to see if it’s the source of the problem:

1. Go to the properties of your send port.
2. Click on “Configure…” next to your HTTP type.
3. Choose the “Authentication” tab.
4. Make sure your authentication is set correctly (i.e. valid username and password).

It’s best to not use anonymous authentication.  Basic is good for development and testing, but the preferred method is either Kerberos or Single Sign-On.

Best of luck!