Extraction Errors

Troubleshooting extraction errors - check for the following:

Changes in the Source Table Structure.

Issue. ETL fails to extract using the old table structure.

Diagnosis. The target table on the ETL Objects panel has columns that are no longer available in the source table from the ETL Source panel.

Fix. Re-add the table to ETL Objects with a different name and load the data.
If the load is successful delete the old table, and rename the new table to replace the old one.

• Changes to a table’s structure might also affect usage downstream such as Tableau .tds extracts or Excel reports.

Values are not accepted by MS SQL

Diagnosis:

• An invalid date (click here to learn more) loaded in a Date column.

• A character is loaded into an integer column, e.g., it should be char(10) but it's configured int. This data type mismatch is possible when using ETL+ data sources like Excel, Google Sheets, and OData or when the target column was misconfigured.

  • See ETL+ Issues Importing from Excel for more on data type issues with Excel data sources.

Fix

• Fix the value in the source table.

• Change the format of the target column.

• Filter the offending records out in the ETL SQL Statement.

Source system or Source Driver instability

Fix: Sometimes the issue can only be resolved by rebooting the source system’s server, or resetting the driver (ex.: ODBC) used to connect to the source system.

Acumatica OData Testing

When an OData extraction fails in ETL+, first, see the sections OData v3 and v4 below for additional tips. If those tips don’t resolve the issue, follow the procedure below to test if the issue is in Acumatica or ETL+:

1. Copy the OData URL from Odata feed (URL) on the ETL+ OData source (see image below).

2. Append “/<GI or DAC name of the table with issues>” to the URL, e.g., https://acu.dataself/odatav4/Account.

3. Paste that string onto a web browser's URL box.

4. Press Enter on the browser, enter credentials, Ok.

Wait until the extraction of all rows ends. Potential results:

• If it fails or ends in the middle of a row, the problem is with Acumatica: Contact your Acumatica VAR.

• If it ends successfully at the end of the extraction: contact DataSelf support.

Open

Acumatica OData v3 (Generic Inquiries, GIs)

Issue: When extracting data from GIs, calculations with invalid amounts might fail the OData extraction (for instance, a division by zero).

Fix: We recommend 1) bring your raw data to the data warehouse and then do transformations (such as calculations) in the data warehouse or the reporting tool or 2) use conditional statements to avoid invalid amounts in GIs.

Acumatica OData v4

• If a table is not available to be extracted, create a new table in ETL+, and insert/type in the SELECT statement for that table, e.g., SELECT DocType, InvoiceNbr, LineTotal FROM ARInvoice.

  • Sometimes, you’ll have to manually set the table columns' formatting on the ETL+ Design page.

  • Sometimes, you’ll have to use the DAC Object name instead of the table name on the FROM section. E.g. FROM [PX_Objects_AR_ARInvoice]. In some installs, this approach fixes some some columns that were coming all NULL with the table name.

  • For some Acumatica installations where OData v4 fails to operate as expected, an Acumatica server reboot fixes the OData v4 issues.

  • If the above doesn’t work, try to extract the table with issues via OData v3 (via a GI).