Mark Wilson I am the creator of TopXML. I am available for international and local (Australia) contracts. I am a Solution Architect/Business Analyst. I have worked in IT in several countries (NZ, Australia, South Africa, UK) building and training teams for government and very large non-governmental organizations. I am ex-Microsoft Consulting Services. I wrote the first book on Microsoft XML published in 2000 called XML Programming with VB and ASP. Most recently I have been building tools for the SEO industry. Ask me for a 37 point SEO health-checkup for your website.
First posted :
03/24/2008
Times viewed :
415
BizTalk: Creating an Oracle Send Port
The BizTalk
Utilities Database Adapter provides an extensive set of features enabling
two-way data exchange between BizTalk Server and Oracle data sources.
Click for an overview of BizTalk
and Oracle.
Overview
The aim of
the document is to provide step-by-step instructions for creating an Oracle
Send Port for updating a Table within Visual Studio .NET and configuring the
Port within BizTalk Explorer.
The Oracle
Table being used is OE.PRODUCT_INFORMATION, a
sample table provided by the default Oracle Sample Database.
1. Start
Visual Studio .NET and create a new BizTalk Server Project. For this sample we
will call the Project Products.
2. Right-Click
on the Products Project and select Add/Generated Items…
3. Select
Add Adapter and Click on the Open Button.
4. Select
the BizTalk
Utilities Adapter for Databases Transport Adapter from the List,
and click on the Next Button.
5. The
Welcome screen for the Database Schema Wizard Appears. Click on the Next
Button.
6. You
will now be prompted for the Data Provider to use. Because we want to send data
to an Oracle Database, we .will select the Microsoft .NET Data Provider for
Oracle item and click on the Next Button.
7. The
Wizard now requires you to enter a Connection String for the Oracle Database
from which the Database Schema needs to be generated. The easiest way to
configure the Connection String is to click on the Set button and configure the
Connection String with the Data Link Dialog.
8. The
easiest way to configure the Connection String is to click on the Set button
and configure the Connection String with the Data Link Dialog.
9. Enter
the name of your Oracle Server as well as the User Information with which you
want to Log on. Please remember to check the Allow saving Password option so
that the Connection String contains the Password that you have specified.
10. To
test the connection you can click on the Test Connection button to see if the
information that you have entered is correct.
11. Once
you are happy with the Connection Information you can click on the OK button to
save your changes.
12. The
Wizard Page will now contain the Connection String that you specified. You can
click on the Next Button to continue.
13. Because
we want to send Data to the Oracle Database, select Send Port in the Port Type
Dialog and click on the Next Button to continue.
14. The
Wizard now needs to know which Tables and Stored Procedures you want to use
within the generated Database Schema.
15. In
this case we want to access the Products table. We will add a new object to the
List by Right-Clicking and selecting Add…
16. The
Command Wizard will now appear.
17. We
want to add the Table OE.PRODUCT_INFORMATION, so
we will enter the name of the Table into the Wizard and Select Table as the
Command Type. Because the Products Table is located in the SH Schema, we need
to prefix the Table name with SH.
18. Click
on the Next Button to continue.
19. Our
Commands Dialog now contains the OE.PRODUCT_INFORMATION table.
20. If we
wanted to add more Tables or Stored Procedures within the Database Schema. We
could run the Command Wizard again.
21. Click
on the Next Button to Continue.
22. In the
Options Dialog, enter the name of the Root Node for the Database Schema and
enter a Target Namespace for the Schema.
23. Click
on the Finish Button to generate the Database Schema.
24. The
Database Schema will now be generated and added to the Products BizTalk Server
Project.
25.
Certain
Errors may occur during the Schema Generation Process. You will be prompted
with the Error Information and asked if you want to run the Wizard Again to
correct any information that you have supplied to the Wizard.
Creating the Send Port on
the Orchestration
1. Open
the Orchestration that was created by the Database Schema Wizard. This is
usually called BizTalk Orchestration.odx.
2. When
calling the Adapter you will Send Data and receive a response. Add a Send and
Receive Shape to the Orchestration.
3. No we
can add a Port for calling the Adapter. Right-Click on the Port Surface Area
and select New Configured Port…
4. The
Port Configuration Wizard will appear. Click on the Next Button to Continue.
5. Enter
a Name for the Port. In this example we will keep the Default name Port_1.
Click on the Next button to continue.
6. Select
the Use an existing Port Type Radio button and select
Products.PRODUCT_INFORMATIONPortType as the Port Type. Click on the Next Button
to Continue.
7. In the
direction drop-down select “I’ll be sending a request and receiving a
response.” option. In the binding drop-down select “Specify Later”. Click on
the Next Button to Continue.
8. Click
on the Finish Button on the Summary Dialog to complete the Configuration
Process.
9. The
Orchestration should now have a new Port called Port_1.
10. Attach
the Send Shape to the Request Method and the Receive Shape to the Response
Method.
Creating the Send Port in
the BizTalk Explorer
1. Open
the BizTalk Explorer by Clicking View/BizTalk Explorer within Visual Studio .NET.
2. Navigate
to Send Ports.
3. Right
Click on the Send Ports node and select Add Send Port from the Context Menu.
4. Select
Static Solicit-Response Port from the type drop-down.
5. Select
the BizTalk
Utilities Adapter for Databases Transport from the Transport Type
Dialog.
6. Click
on the Editor Button (…) when selecting the Address (URI) option.
7. Select
the Oracle Provider within the Data Provider drop-down.
8. Configure
the Oracle Connection string by clicking on the Editor (…) button.
9. Click
on the OK button to continue.
10. Under
Send General, select the default Microsoft XML Pipelines for the Send and
Receive pipelines.
11. Click
OK to complete the configuration of the Send Port.
Summary
The BizTalk
Utilities Database Adapter provides an extensive set of features enabling
two-way data exchange between BizTalk Server and Oracle data sources. When
you need to extend your integrated environment in order to make BizTalk and
Oracle cooperate, there is no easier way of accomplishing this than by enlisting
BizTalk Utilities.
To learn more about these and other features, download an evaluation copy of BizTalk
Utilities
Email TopXML