ETL+ for Sage 100 Providex

Owned by Joni Girardi, created
Last updated: Dec 21, 2023 by Cortlandt _
3 min read

See also https://dataself.atlassian.net/wiki/spaces/DS/pages/1666482584

DataSelf has been working and optimizing the Sage 100 Providex data extraction since 2005.

Introduction to DataSelf

Installation Checklist

ETL+ Connection to Providex ODBC (single company)

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

  2. See https://dataself.atlassian.net/wiki/spaces/DS/pages/7831651 for how to configure the ODBC driver.

  3. Sign in (Login) to ETL+.

  4. Select the Sage 100 object on the Source Objects panel.

  5. Select Properties on the Source Objects panel.

    • If the Microsoft SQL Server (instead of ODBC) page pops up,
      click Change Data Source (wrench icon) > ODBC.

  6. Select the correct Providex ODBC driver listed on the ODBC DataSources page.

    • Can’t find your ODBC? It’s likely because your Providex ODBC is 32-bit: Download the DataSelf ETL+ 32bits file from here. Then copy/paste the downloaded file into C:\Program Files\DataSelf ETL Plus. Confirm the overwrite prompt.

    • If it fails, try adding the ODBC credentials.

  7. Customize the Alias as desired.

  8. Click Connect.

  9. You’ll return to the ETL page and see tables under the Sage 100 source on the left panel.

Testing a Table Load

Right-click a table on ETL main page’s center panel → Load Now.

  • Failure? It’s likely that the prior step wasn’t properly configured. Go back and review it.

  • 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).

  • The Job page usually comes pre-configured to your source system(s).

    • Overview this page’s configuration. On the right panel, feel free to uncheck tables and Tableau extracts that don’t apply to your reporting needs.

  • Run a Job now (optional): click the Run Job (play icon) on the left panel.

  • To schedule the data load (optional):

    • Select a Job → Job Properties (wrench icon) → configure the schedule panel → Save

  • Click Close -> Close.

Level 1 Testing

The Level 1 Testing checks if the data extraction process finished successfully and row count per extracted table matches with Sage 100’s table row counts.

ETL+ must have already loaded your data manually or scheduled.

Steps

  • Login to ETL+.

  • 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 the row count matches with the Sage 100 tables. Following is an example highlighting two tables and their loaded row count.

Troubleshooting

  • Failed to load tables:

    • Check that the user assigned to DataSelf has reading rights to that particular data set.

    • Click Back from the Log page, on the ETL main page, select the failing table on the center panel, check that its SQL Statement on the right panel has correct information.

  • Issues loading Date columns with invalid values for SQL:

  • Tables with record count different than in Sage 100:

    • It might be just that new records have been populated in Sage 100 since the DataSelf data load. Reload the data. Click Back from the Log page, on the ETL main page, select the table with incorrect row cound on the center panel, right-click the table on the center panel again -> Load Now. This might take a while if the table has a large data set.

    • The user assigned to DataSelf might have access to limited data sets, such as only data from one Division. Check the user’s security credentials.

Premapped ETL+ Tables

Providex Documentation

  • SQL syntax for Sage 100
    SQL syntax to use in the