Thursday, December 22, 2011

Using the Community ODBC Adapter from TWOCONNECT

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.

  1. Request\Response operation using a Select from a table.
  2. Request operation using an Update on a table.
  3. 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

image

 

 

 

 

 

 

 

 

 

We will have to manually add the Adapter to our BizTalk Environment as shown below:

image

 

 

 

 

 

 

 

We start off with creating a new 32 Bit Host and Host Instance as shown below:

image

 

 

 

 

 

 

 

 

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

image

 

 

 

 

 

 

 

Assign ODBCHost to Receive Handler

image

 

 

 

 

 

 

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.

image

 

 

 

 

 

 

 

We create a new System Data Source as shown below

image

 

 

 

 

 

 

 

 

 

Clicking “Add” starts the Configuration Wizard.

Enter a name for our Data Source.

Select our SQL Server.

image

 

 

 

 

 

 

 

 

Click “Next” until you get to the page show below.

image

 

 

 

 

 

 

 

 

Check the “Change the default database to:” and select our PurchaseOrders Database.

Click “Next” again.

We can review our configuration as shown below.

image

 

 

 

 

 

 

 

 

 

 

 

Click on “Test Data Source” to test our connection as shown below.

image

 

 

 

 

 

 

 

 

 

 

 

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.

image

 

 

 

 

2. Select the ODBC Adapter as shown below:

image

 

 

 

 

 

 

 

 

3. Click “Next”.

4. Then enter our Target Namespace, Request and Response element root names as shown below.

image

 

 

 

 

 

 

 

 

5. Click “Next”.

6. We will be doing a simple Select Statement. Select “SQL Script” and click “Next” as shown below

image

 

 

 

 

 

 

 

 

7. On the Statement Information page, we set the “Query Type” to Select.

8. Next we will enter our Select Statement as shown below.

image

 

 

 

 

 

 

 

 

9. Next Click “Generate”.

After a few seconds you will see two schemas and an orchestration in your project folder.

image

 

 

 

 

 

10. Since we did a Select without a where clause, our generated schema is generic as shown below.

image

 

 

 

 

 

11. The Response Schema contains all the fields from our table.

image

 

 

 

 

 

 

 

 

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.

image

 

 

 

 

 

 

14. Another issue is that there are two ports created instead of one as shown below.(This is also an issue with the adapter).

image

 

 

 

 

 

 

 

 

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.

image

 

 

 

 

 

 

 

 

image

 

 

 

 

 

16. Next we need to add our Inbound Message. We are using the Request Message generated by the Adapter Wizard.

image

 

 

 

 

 

 

17. The following is our Port Binding.

image

 

 

 

 

 

18. The completed Orchestration for our first demo is shown below.

image

 

 

 

 

 

 

 

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

image

 

 

 

 

 

 

 

 

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.

image

 

 

 

 

 

 

 

 

 

8. We have to select each Parameter and set its Type and Field Size. Setting the Sample Data value is optional.

image

 

 

 

 

 

9. Once completed we click on “Update Command”.

image

 

 

 

 

 

 

10. This brings us back to the Schema Generation page.

11. We can now click on “Generate”.

image

 

 

 

 

 

 

 

 

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

image

 

 

 

 

 

 

14. Our generated schema is shown below

image

 

 

 

 

 

 

 

 

image

 

 

 

 

 

 

 

15. As you can see the correct data types are set for each parameter.

image

 

 

 

 

 

 

 

17. Our Orchestration is similar to our first demo, the only exception being a Send Port instead of a Send\Receive

Update Orchestration

 

 

 

 

 

 

Request\Response operation using a stored procedure

 

1. We create a new stored procedure as shown below.

image

 

 

 

 

 

 

 

 

 

 

 

 

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.

image

 

 

 

 

 

 

 

 

5. Click “Generate”

image

 

 

 

 

 

 

 

 

6. This generates two schemas and an orchestration as shown below.

image

 

 

 

 

 

 

Our Request Schema

image

 

 

 

 

 

 

 

 

image

 

 

 

 

 

 

 

 

 

Our Response Schema

image

 

 

 

 

 

We still have the same issue with the Multipart Message Types.

7. We set the correct schemas again.

image

 

 

 

 

 

 

 

 

 

8. Two Port Types are created. We need to delete these and create a new Request/Response Port.

image

 

 

 

 

 

9. Creating our new Request/Response Port

image

 

 

 

 

 

 

 

 

10. We are sending a Request and Receiving a response

image

 

 

 

 

11. Our new Request\Response Port as show below.

image

 

 

 

 

 

12. Next we create our Orchestration as shown below.

image

 

 

 

 

 

 

 

 

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.

image

 

 

 

 

 

2. After deploying our demos, we need to configure a Static Solicit-Response Send Port as shown below.

image

 

 

 

 

 

 

 

 

 

3. Since we are using Windows Authentication for our ODBC Data Sources, we uncheck “Include UserID and Password” as shown below.

image

 

 

 

 

 

 

 

 

4. We select our Data Source Name and click “OK”.

5. Our ODBC Transport Properties are set as shown below.

image

 

 

 

 

 

 

 

 

 

 

 

6. We will also need to create a static one-way port a shown below.

image

 

 

 

 

 

 

 

 

7. We configure the Send Port by setting our Connection String.

8. We then click on the Connection String button.

image

 

 

 

 

 

 

 

 

 

 

 

9. We select our ODBC_Test Data Source as shown below

image

 

 

 

 

 

 

 

 

If our ODBC Data Source supports Transactions, you can select from the “Transaction type to be applied” list as shown below.

image

 

 

 

 

 

 

 

 

 

 

 

10. In the case of our scenario, we will leave it set to the default “None”

.image

 

 

 

 

 

 

 

 

 

 

 

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

Technorati Tags: ,,

2 comments:

  1. Interesting Article, you have some valid points

    ReplyDelete
  2. hey Howard,
    I am not able to see any images posted on this blog.
    I am trying to set data polling receive location to pull data from MySQL..is it possible ? bz I am getting errors.

    ReplyDelete