Having previously detailed the installation of the Community ODBC Adapter for BizTalk 2009, the next thing I will be looking at is the generation of schemas using this ODBC adapter.

Within your BizTalk 2009 project, right click the project and select Add Generated Items.  In the resultant window choose Add Adapter Metadata and click Add to open the Add Adapter Wizard.

Check that the BizTalk Server and Database names are correct, select the ODBC adapter and click next.

You must now set the connection string. To start with choose set, then new DSN (data source name).

You now need to define the Data Source you will be connecting to.  On the User DSN tab select Add add then driver you want to use. In this case I am going to use the MySQL ODBC Driver.  A User DSN will only be visible on the current machine with you as a user.

* Although I initially set up a User DSN and this was fine for creating schemas with, I later realised that you actually need a system DSN as the BizTalk host service needs this to be able connect to the database on a receive or send port.

You will then be asked to Set up the MySQL ODBC Data Source.  In my case this is a local database making use of named pipes, so I had to make sure that I ticked the "Force use of named pipes" check box and removed the "# The Pipe the MySQL Server will use socket=mysql" line from the mysql.ini; with this is place the connection would fail as there is no apparent way to specify the pipe name in the ODBC driver configuration.

This will then update the User DSN tab with the new Data Source.  Make sure that you select it and press OK.

Select it again in the Choose Data Source window and press OK.  On the ODBC transport window select next.

You will now be presented with the Schema Information window, where you must supply the namespace, type and root element names for your schema.

Next choose the type of statement that you will be using to create your schema - in this case I am using a stored procedure.

*I later discovered that this option is fine for MySQL stored procedures without input parameters, but failed for MySQL stored procedures with input parameters.  (I will be posting on the way to handle input parameters soon)

Next you will need to specify the name of the stored procedure.  In this case I have a simple stored procedure to return all the data held by my TestTable in MySQL.

Select * from TestTable;

The table itself has three columns: Name, Sex and Married.

Selecting finish should now hopefully create your schemas based on the input and output from your stored procedure.

In my case I have: 

•  An empty schema for the request; after all I have no parameters for the stored procedure.
•  A response schema comprised of a Table Record with Name, Sex and Married children.

Next I will be looking at the use of the ODBC adpater with:

• Receive ports
• Send ports