Sage 100 Providex Extraction - Troubleshooting

How to configure ETL+ to extract invalid Providex data into a MS SQL data warehouse.

Invalid Dates

Approach 1

This approach usually addresses ETL+ logged errors such as: “SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM”.

ETL+ Design Page will convert data from Sage 100 Providex to a format usable on ETL+ data warehouse (a Microsoft SQL Server database).

Double click on a Providex table on the panel IF
the table hasn’t yet been added to the panel.
Select the target table on the panel.
Select Design.
For the date columns in the target table:
1. Select the VarChar checkbox.
2. Change Dw Data Type to datetime.

ETL+ will generate and enter a appropriate T-SQL Expression.

**Design** page enters a value for **T-SQL Expression**: `TRY_CONVERT(datetime, [<`*datetime_column*`>])`

Approach 2

For some invalid date issues such as logged by ETL+: “Year, Month, and Day parameters describe an un-representable DateTime” are triggered by the Providex ODBC. To fix them in ETL+, change the ETL Extract SQL so the ODBC converts the original date value into a varchar column.

For every date column on tables failing to extract, customize the Extract SQL to transform them as shown below:

{fn convert(DateColumnName1, SQL_VARCHAR)} as DateColumnName2,
Then use ETL+ Design to convert the column back to a DateTime data type.

Approach 3

Use ETL+ Extract SQL for filtering out invalid date ranges. This won’t work if there are invalid calendar values such as 02/30/2025 (Feb with 30 days), 04/32/2025, and 14/01/2025 (month 14).

E.g.: WHERE DateField1 >= {d'1990-01-01'} AND DateField2 >= {d'1990-01-01'}.

Invalid Numeric Data Types

You might need to use ETL+ Design page to convert Sage 100 invalid data types such as Decimall(99,0) to decimal(18,0) or similar.

Other Sage 100 Date Tips

ETL+ Load Replace – special uses with configuring dates from GETDATE() function.
Sage 100 Providex Dates – Transforming problematic dates in the Design page.
Providex SQL Syntax – tips for working with dates in the Extract SQL Statement panel.
ETL+ Load Replace with Date-time fields – when to use CONVERT(DATE, GETDATE()) instead of GETDATE().

Sage 100 Technical Details – issues addressed in more depth
Sage 100 Providex Date Functions
ETL+ for Sage 100 Providex – more pages in this section

From Sage

– Guide to finding SQL documentation in this reference manual.
http://manual.pvxplus.com – Link to the reference manual itself