Business Data Catalog ODBC connection

We have had the ability to connect to ODBC data sources using a DSN name for quite some time - but unfortunately some ODBC drivers do not support this kind of ODBC connection and require that username and password are hard coded in a connection string. We've now added the ability for you to type any ODBC connection string you need. A good resource of connection strings can be found here:

www.connectionstrings.com

One of the issues with ODBC is that different drivers and data sources require different delimiter characters for their column names and parameter symbols. We'll see in BDC Meta Man how we need to set these at the initial connection setup.

For this walk through we are going to connect to MS SQL Server. We do have support for SQL Server and the BDC through the native .net SQL client, but it is as good as any data source to use as an example.

1, Open BDC Meta Man and click the drop down for the Connect to data source button

2, Select ODBC - Connection String from the drop down

3, We now have a textbox to enter our ODBC connection string. As we are using SQL Server my connection string will be:

Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase; Uid=myUsername;Pwd=myPassword;

Before clicking the Connect button we need to complete the advanced properties, so click on the Advanced button

4, We need to enter the left and right column delimiter symbols, and also the parameter character. For MS SQL Server these will be:

Left Delimiter Symbol = [

Right Delimiter Symbol = ]

Parameter Symbol = ?

These characters will vary from data source to data source so you may need to do some investigation into the SDK for your particular ODBC source.

5, BDC Meta Man will now connect to your ODBC data source and display the database. Clicking the plus sign next to the database name will expand it to show all the tables available. You can simply drag and drop these onto your design surface as any normal entity. To see more information on this check out the BDC Getting Started walkthrough and screencast.