BizTalk 2010 does not have an adapter for every Data Resource. We have a choice of either purchasing an Adapter, creating our own by using the WCF LOB SDK, or using the Community ODBC Adapter provided by TWOCONNECT
TWOCONNECT has updated the open source ODBC Adapter made on GotDotNet several years ago, to work with BizTalk 2010. It has been updated to support the Net 4.0 Target and the Install Folder Path has been fixed for 64 Bit Systems.
According to TWOCONNECT:
- It has support for creating two way operations
- Can no longer be configured on a Request/Response Port
- Poll While Data Found option on Receive Location is supported.
- Performance Counters and Event Source created at install time.
- No longer requires Administrator rights at Runtime
The adapter does have limitations and issues:
- When using Insert, Update, or Delete Table Operations, there is no return value. It only supports a one-way operation.
- You cannot use Output parameters in Stored Procedures
To demonstrate the use of the Adapter, I have created three scenarios.
- Request\Response operation using a Select from a table.
- Request operation using an Update on a table.
- Request\Response operation using a stored procedure
For the scenarios, we are going to use SQL 2008 R2 Server Database as our ODBC Source.
Before we can use the Adapter we need to install it.
Installation and Configuration
Installation is fairly straight forward
We will have to manually add the Adapter to our BizTalk Environment as shown below:
We start off with creating a new 32 Bit Host and Host Instance as shown below:
We then assign our ODBC Host Instance to the ODBC Adapter’s Send and Receive Handlers as shown below.
Assign our ODBCHost to Send Handler
Assign ODBCHost to Receive Handler
Setting our ODBC Data Source
Assuming that our BizTalk 2010 Environment is 64 bit, we will need to use the odbccad32.exe, which is located in C:\Windows\SysWOW32, to create your ODBC Data Source.
We create a new System Data Source as shown below
Clicking “Add” starts the Configuration Wizard.
Enter a name for our Data Source.
Select our SQL Server.
Click “Next” until you get to the page show below.
Check the “Change the default database to:” and select our PurchaseOrders Database.
Click “Next” again.
We can review our configuration as shown below.
Click on “Test Data Source” to test our connection as shown below.
If we configured everything correctly, we will see the results as show above.
Scenarios
Request\Response operation using a Select from a table
Open Visual Studio 2010 and create a new BizTalk Application. Name it “ODBC_Sample”.
1. Next we will use the “Add Adapter Metadata Wizard” to generate our schemas and orchestration as shown below.
2. Select the ODBC Adapter as shown below:
3. Click “Next”.
4. Then enter our Target Namespace, Request and Response element root names as shown below.
5. Click “Next”.
6. We will be doing a simple Select Statement. Select “SQL Script” and click “Next” as shown below
7. On the Statement Information page, we set the “Query Type” to Select.
8. Next we will enter our Select Statement as shown below.
9. Next Click “Generate”.
After a few seconds you will see two schemas and an orchestration in your project folder.
10. Since we did a Select without a where clause, our generated schema is generic as shown below.
11. The Response Schema contains all the fields from our table.
12. Looking at the Multipart Message Types, we can see that there is a problem with the Multi-part Message Types. (This is one of the issues in the Adapter)
13. To correct this, we will need to re-assign the schemas to the Message Type.
14. Another issue is that there are two ports created instead of one as shown below.(This is also an issue with the adapter).
15. The solution to this issue is to delete the ODBCRequest and ODBCResponse Port Types and create a new Request-Response Port as shown below.
16. Next we need to add our Inbound Message. We are using the Request Message generated by the Adapter Wizard.
17. The following is our Port Binding.
18. The completed Orchestration for our first demo is shown below.
Request operation using an Update on a table.
We will need to repeat Steps 1 – 5 .
6. In order to do an Update, we need to manually enter our Update Query into the Query Editor as shown below
7. Before we click on “Generate”, we need to check the “Override default query processing” If we omit this step, all of our Input Parameters will default to an NVarChar Data Type with a field size of 1. We would have to start all over again with Step 1.
8. We have to select each Parameter and set its Type and Field Size. Setting the Sample Data value is optional.
9. Once completed we click on “Update Command”.
10. This brings us back to the Schema Generation page.
11. We can now click on “Generate”.
12. This time we have one Schema and an Orchestration generated.
We also have the same issue with Multi-part Message Types.
13. We need to set the Message type to the correct schema
14. Our generated schema is shown below
15. As you can see the correct data types are set for each parameter.
17. Our Orchestration is similar to our first demo, the only exception being a Send Port instead of a Send\Receive
Request\Response operation using a stored procedure
1. We create a new stored procedure as shown below.
2. Next we must follow Steps 1 - 5 as in the previous demos.
3. On the Statement Type page , we will select Stored Procedure.
4 On the Statement Information page, we enter its name as shown below.
5. Click “Generate”
6. This generates two schemas and an orchestration as shown below.
Our Request Schema
Our Response Schema
We still have the same issue with the Multipart Message Types.
7. We set the correct schemas again.
8. Two Port Types are created. We need to delete these and create a new Request/Response Port.
9. Creating our new Request/Response Port
10. We are sending a Request and Receiving a response
11. Our new Request\Response Port as show below.
12. Next we create our Orchestration as shown below.
Because we are using a Stored Procedure that returns a value, we can add Exception Handling. In this case we are validating the result and throwing a System.Exception if the Stored Procedure returns an Error Code.
Deployment
We will need to create two Send Ports, a Static Request-Response for our Select Table Operation and Stored Procedure Demos, and a Static Request Port for our Update Table Operation.
Creating Ports for the ODBC Adapter
1. Open the BizTalk 2010 Administration Console and add a new Application called “ODBC Test” as shown below.
2. After deploying our demos, we need to configure a Static Solicit-Response Send Port as shown below.
3. Since we are using Windows Authentication for our ODBC Data Sources, we uncheck “Include UserID and Password” as shown below.
4. We select our Data Source Name and click “OK”.
5. Our ODBC Transport Properties are set as shown below.
6. We will also need to create a static one-way port a shown below.
7. We configure the Send Port by setting our Connection String.
8. We then click on the Connection String button.
9. We select our ODBC_Test Data Source as shown below
If our ODBC Data Source supports Transactions, you can select from the “Transaction type to be applied” list as shown below.
10. In the case of our scenario, we will leave it set to the default “None”
.
11. We will need to create a Receive Port and Location using the File Adapter for each Orchestration
12. We then Bind our Orchestrations to our new Ports and our ready for testing.
Summary
- We have leaned about the capabilities and limitations of the Community ODBC Adapter.
- We have learned about the Port Types and Multi-part Message Types issues and how to resolve them.
- We have walked through three different Design Time scenarios on using the Adapter.
- We have learned how to configure our Ports for the Adapter
