Geeks With Blogs
Stuart Brierley Integration Management - BizTalk and More

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.

BizTalk 2009 ODBC Adapter Schema Generation - Add

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

BizTalk 2009 ODBC Adapter Schema Generation - Adapter

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

BizTalk 2009 ODBC Adapter Schema Generation - Set Connection String

BizTalk 2009 ODBC Adapter Schema Generation - Data Source

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.

BizTalk 2009 ODBC Adapter Schema Generation - User DSN
BizTalk 2009 ODBC Adapter Schema Generation - ODBC Driver

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.

BizTalk 2009 ODBC Adpater Schema Generation - MySQL Data Source

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

BizTalk 2009 ODBC Adpater Schema Generation - User DSN Updated

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

BizTalk 2009 ODBC Adpater Schema Generation - Updated Data Sources


BizTalk 2009 ODBC Adpater Schema Generation - Updated ODBC Transport

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

BizTalk 2009 ODBC Adpater Schema Generation - Schema Information

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.  For details on a fix for this read my new post on generating schemas with parameters.

BizTalk 2009 ODBC Adpater Schema Generation - ODBC Statement Type

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.

BizTalk 2009 ODBC Adpater Schema Generation - ODBC Statement

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.

BizTalk 2009 ODBC Adpater Schema Generation - ODBC Response Schema

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

Posted on Wednesday, June 9, 2010 1:54 PM BizTalk Adapters | Back to top


Comments on this post: BizTalk 2009 - The Community ODBC Adapter: Schema Generation

# re: BizTalk 2009 - The Community ODBC Adapter: Schema Generation
Requesting Gravatar...
How to create SQL store procedure schema with parameter ?
Left by joe on Apr 22, 2011 7:50 AM

# re: BizTalk 2009 - The Community ODBC Adapter: Schema Generation
Requesting Gravatar...
Hi,

Have you taken a look at this post?

http://geekswithblogs.net/StuartBrierley/archive/2010/06/09/biztalk-2009---the-community-odbc-adapter-schema-generation-with.aspx

It shows how to generate a schema from a MySQL stored procedure that has parameters.

Cheers
Stu
Left by Stuart Brierley on Apr 22, 2011 8:24 AM

Your comment:
 (will show your gravatar)


Copyright © Stuart Brierley | Powered by: GeeksWithBlogs.net