ETL Troubleshooting Guide

The Extract, Transform, Load (ETL) process enables organizations to import, transform, and load data into ACE. Successful ETL imports depend on properly formatted files, accurate field mappings, and correctly configured ETL profiles.

This guide explains common ETL issues, troubleshooting steps, and the tools available to help identify and resolve import problems.

Common ETL Issues

Step-by-Step Troubleshooting

Review ETL Import History

Begin troubleshooting by reviewing the ETL Import History.

1. Navigate to Transactions → ETL Import → History.

2. On the Imports list screen, locate the file you want to analyze.

3. Click the file name to open the Delta Log page, which contains:

   1. Delta Status: Completion status (e.g., COMPLETED, FAILED).

   2. Start Time, End Time, and Duration: Identify timing issues.

   3. Error Count: Review the number of errors logged.

   4. Duplicate Count: Track duplicates that may disrupt imports.

4. Use the Download Messages button to export detailed logs, including warnings, errors, and impacted rows.

Resolve File Formatting Errors

Symptoms

Resolution

Verify the file meets the following requirements.

Also verify:

• Column headers match the expected layout.

• Invalid characters have been removed.

• Required data is present.

Correct the file and run the ETL again.

Resolve Transformation Failures

Symptoms

• Field mapping failed

• Imported data appears in incorrect fields

Resolution

1. Go to Accounts → ETL Import → Profiles.

2. Open the ETL profile used by the import.

3. Review the Data Map section.

4. Verify all required fields are mapped correctly.

5. Save the profile.

6. Run the ETL again.

If prompted to update custom columns, confirm the changes and map any new fields before rerunning the import. See Handling Custom Columns in ETL Profile Updates for details.

Resolve Load Errors

Symptoms

• Duplicate key violation

• Schema mismatch

• Import completed with errors

Resolution

1. Review the Delta Log to identify the affected records.

2. Download the error messages.

3. Remove duplicate records if necessary.

4. Verify source data matches the required field types and formats.

5. Correct the source file.

6. Run the ETL again.

For legacy imports, temporarily disabling Bundling or Statute of Limitations (SOL) processing may help isolate the issue.

Resolve Performance Issues

Symptoms

• ETL runs slowly.

• Import stops before completion.

• Large files take excessive time to process.

Resolution

• Enable Multithreading in the ETL profile when appropriate.

• Configure Maximum ETL Threads under Setup → System (recommended: 10–30).

• Split large imports into smaller files.

• Run large imports during off-peak hours.

• Monitor CPU and memory usage. See System Metrics for additional information. See ACE System Metrics: Tracking CPU and Memory Usage for more information.

Resolve Corrupted Files

Symptoms

• File could not be read.

• Unexpected file termination.

• File cannot be opened correctly.

Resolution

1. Verify the file opens successfully in a spreadsheet application or text editor.

2. Re-export the file from the original source if necessary.

3. Remove invalid characters or damaged rows.

4. Save the file as a new CSV or TSV file.

5. Test the corrected file before importing the full dataset.

Resolve Excel Validation Errors

Symptoms

• Excel workbook rejected during upload.

• Warning indicating formulas or functions are not supported.

Resolution

1. Remove all formulas and functions from the workbook.

2. Save the workbook.

3. Convert the workbook to a CSV file when possible.

4. Import the updated file.

Key Tools

Delta Log

The Delta Log provides detailed information about each ETL import, including:

• Status

• Error Count

• Duplicate Count

• Records Loaded

• Rollback option (when supported)

Note

Accounts with existing transaction history cannot be rolled back.

Multithreading

Multithreading processes multiple records simultaneously, reducing import times for large files.

Use multithreading for large imports such as:

• Account Assignments

• Transactions

• Account Flags

Caution

Do not enable multithreading if the order of rows in the source file is important.

ETL Profiles

ETL Profiles store reusable import configurations, including field mappings and file settings.

To create a profile:

1. Go to Accounts → ETL Import → Profiles.

2. Select New.

3. Upload a sample file.

4. Configure the field mappings.

5. Save the profile.

Preventing ETL Issues

To reduce ETL failures:

• Use approved Import Layouts.

• Review ETL Profiles regularly.

• Run large imports during off-peak hours.

• Use the On Errors Notify User ID setting to notify administrators of failed imports.

• Use CSV files whenever possible for the best performance.

Example Scenarios

Scenario 1: Duplicate Key Violation

• Cause: The source file contains duplicate Debt IDs.

• Solution:

  • Open the Delta Log and download error messages.

  • Deduplicate the file using Excel or SQL.

  • Reload the data.

Scenario 2: File Fails to Load Due to Schema Mismatch

• Cause: Field types in the file (e.g., text instead of numeric) don’t match ACE requirements.

• Solution:

  • Correct the mismatched fields in the file.

  • Reload the job and verify the data mapping.

Contacting Support

If the issue persists, contact InterProse Support and include the following information:

• A screenshot of the entire page

• The browser URL

• A description of the task being performed

• Steps to reproduce the issue

• The complete error message, if available