Skip to end of banner
Go to start of banner

ETL+ for ODBC (DSN)

Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 9 Current »

Installation Checklist

ETL+ Connection to ODBC

  • ODBC driver to use in ETL+:

    • Have your source system’s ODBC driver on the same Windows computer where ETL+ is ( or is going to be installed).

    • Create or use an ODBC System DSN (instead of User DSN) to your source system

    • The ODBC driver must have the same 32/64bit build as ETL+

    • Click here for Microsoft ODBC documentation.

    • To retain OAuth credentials: Look for your ODBC documentation/configuration on how to retain the OAuth credentials. Ex.: Enter the following in CData’s ODBCs → Advanced/Misc/Other: oauthsettingslocation=C:\Users\Public\oauthsettings.txt

  • If you haven’t installed ETL+ yet: ETL+ Deployment Instructions.

  • Login to ETL+ (help here).

  • Adding a new ODBC source to ETL+: On ETL page’s left panel, click Add Source -> ODBC.

  • Maintaining an ODBC source on ETL+: On ETL page’s left panel, right-click the ODBC source → Properties.

  • On the ODBC Data Sources page, select the correct ODBC Name from the list.

    • Can’t find your ODBC? Be sure ETL+ and the ODBC have the same 32/64bit build.

    • ODBC requiring credentials: double-click the ODBC name → enter credentials → Connect.

    • You might leave the Alias as is, or customize it.

    • Click Connect.

  • You’ll get back to the ETL page and see tables under the new ODBC source on the left panel.

Mapping and Loading Tables

  • To view tables available from your ODBC source on the left panel, right-click the ODBC source → Refresh.

  • To map an ODBC table to your data warehouse (ETL Objects), double-click its name on the left panel, or right-click on it → Add Table.

    • You’ll now see the table added/mapped to the center panel (ETL Objects).

  • To preview its data, right-click the table on the center panel → Preview.

  • To load its data to the data warehouse, right-click the table on the center panel → Load Now.

  • You might use the right panel (ETL SQL Statement) to customize the SQL statement. In that case, you must use the ODBC driver’s SQL syntax.

  • Click the Log icon (top right) to view data load results. Log page help here.

Schedule the Data Load

  • On the ETL main page, click Job (top right icon on the center panel).

  • Select a Job or create a new one to load your ODBC source data on a schedule (help here) → on the right panel, be sure the ODBC source tables to load have their boxes checked and in the desired load order.

  • To load them now, click the Run Job Now (play icon) on the left panel for the desired Job.

  • To schedule the data load:

    • Click WTS Settings -> enter the WTS parameters → Apply.

    • This step might require you to run ETL+ app as “Run as administrator” in Windows.

  • Click Close -> Close.

Testing

After ETL+ has loaded your ODBC source tables, this is to check if the data extraction process finished successfully and row count per extracted table matches with your ODBC source tables.

Steps

  • Login to ETL+ (help here).

  • On the ETL main page, click the top-right Log icon. Log page help here.

  • Scroll down on the log page and double-check that no table load failed, and that the row count matches with the source ODBC tables. Following is an example highlighting two tables from a sample ODBC database and their loaded row count.

Related Pages

DSN Related Pages by Source System

  • No labels