Connecting Talend on Windows to an ODBC data source

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 back ends 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:

  1. 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, enter:

    %windir%\syswow64\odbcad32.exe
  2. Download the Easysoft JDBC-ODBC Bridge.
  3. Install and license the Easysoft JDBC-ODBC Bridge on the machine where the Talend is installed.

    For installation instructions, refer to the Easysoft JDBC-ODBC Bridge documentation.

  4. In Talend, create a Standard job.
  5. Drag a tJDBCInput from the component palette to the job window.
  6. 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.
  7. In the Modules tab, choose the Import External Jars button.
  8. 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.

  9. In the Component palette, enter the table that contains the data you want to work with in the Table Name field. For example, Account.
  10. Enter a query that retrieves data from this table in the Query box. For example, "select * from Account".
  11. Choose the Guess schema button.
  12. When prompted to select a mapping file for your database, choose mapping_MsOdbc.xml.
  13. Add a tFileOutputDelimited component.
  14. 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.
  15. Run the job.