Connecting Talend on Windows to an ODBC Database
Talend ships with number of connectors that enable it to integrate data from many popular data stores. Talend can also use a generic connector that enables it to access data from backends for which there is no built-in connector. Talend used to include a JDBC-ODBC Bridge driver, which is the generic middleware that facilitates this type of connection. However, since upgrading the version of Java it ships with, Talend no longer includes a JDBC-ODBC Bridge. To connect to an ODBC data store from Talend you now need to obtain a JDBC-ODBC Bridge from a third party, such as Easysoft.
This blog explains how to use the Easysoft JDBC-ODBC Bridge to connect Talend to an ODBC data store.
The components are:
Talend -> Easysoft JDBC-ODBC Bridge -> ODBC driver -> Database
These steps show how to access some ODBC data from Talend on Windows:
- Configure a System ODBC data source for the database that you want to connect to in Talend.
To do this, use the 32-bit version of ODBC Data Source Administrator on your Talend machine. On some versions of Windows, this is located in Control Panel > Administrative Tools. On some version of Windows, you need to search for ODBC in the taskbar search box. The 32-bit version of ODBC Data Source Administrator should be clearly labelled. If in doubt, in the Windows Run dialog box, type:
%windir%\syswow64\odbcad32.exe
- Download the Easysoft JDBC-ODBC Bridge. (Registration required.)
- Install and license the Easysoft JDBC-ODBC Bridge on the machine where the Talend is installed.
For installation instructions, see the Easysoft JDBC-ODBC Bridge documentation.
- In Talend, create a Standard job.
- Drag a tJDBCInput from the component palette to the job window.
- In the Component tab, set the tJDBCInput's properties:
Note Enclose any text strings you enter in the Component tab in double quotes. For example:
"easysoft.sql.jobDriver"
Property Value JDBC URL jdbc:easysoft://localhost/odbc_data_source:logonuser=windows_user:logonpassword=password Driver JAR EJOB.jar Class Name easysoft.sql.jobDriver Username The user name required to access the data store. Password The password for this user name. - In the Modules tab, choose the Import External Jars button.
- Import the Easysoft JDBC-ODBC Bridge JAR file, EJOB.jar.
EJOB.jar is installed in the following location on the machine where you installed the Easysoft JDBC-ODBC Bridge:
<easysoft_install>\Jars
On 64-bit Windows, the default location for <easysoft_install> is <drive>:\Program Files (x86)\Easysoft Limited\Easysoft JDBC-ODBC Bridge.
- In the Component palette, enter the table that contains the data you want to work with in the Table Name field. For example, "Account".
- Type a query that retrieves data from this table in the Query box. For example,
"select * from Account"
. - Choose the Guess schema button.
- When prompted to select a mapping file for your database, choose
mapping_MsOdbc.xml
. - Add a tFileOutputDelimited component.
- In the tFileOutputDelimited component's properties, choose a file name and location. The file you choose will get populated with data retrieved by the tJDBCInput.
- Run the job.