Troubleshooting & Workarounds

Owned by Neha Awalwar, created
Sept 13, 2024
9 min read

Overview:
This section provides solutions and workarounds for common errors encountered during the data mapping process. It addresses both general mapping issues and specific errors that arise when working with certain connectors like Salesforce, Amazon S3, and Exchange rates, etc. Each error is described in detail, followed by actionable solutions to help you resolve the issue quickly and effectively

Error : Time Data Doesn’t Match Format

  • Error Message:

Error: time data "2024-07-24T18:23:42+0800" doesn't match format "%d.%m.%Y", at position 0. You might want to try: - passing format if your strings have a consistent format; - passing format='ISO8601'

  • Description:
    This error happens when the date and time format in your source data doesn’t match the format expected by the target system. In this case, your source data has the date as "2024-07-24T18:23:42+0800" (ISO 8601 format), but the target system is expecting it in a different format, like "24.07.2024".

  • Solution:

  1. Identify the Formats:

    • The source is giving you the date in ISO 8601 format: "2024-07-24T18:23:42+0800".

    • The target wants the date in the format: "DD.MM.YYYY" (for example, "24.07.2024").

  2. Transform the Date:

    • Use a date format transformation to convert the source date into the format the target expects.

    • For example, in your mapping, apply a transformation that changes "2024-07-24T18:23:42+0800" to "24.07.2024".

  • Alternative Solution:

    • If your target system supports ISO 8601 (the format your source is using), you can just pass the date as it is without converting it. Just make sure the target is set to accept this format.

Error : SQL Compilation Error – Expression Type Does Not Match Column Data Type

  • Error Message:

Error: 'SQL compilation error: Expression type does not match column data type'

  • Description:
    This error occurs when the data type of the value (or expression) you're trying to insert or map into a target column doesn't match the expected data type of that column. For example, you might be trying to insert text into a column that expects numbers, or dates into a column that expects text.

  • Solution:

  1. Check the Data Types:

    • Look at the source data type and the target column type. The source data might be in a different format than what the target column expects.

    • For example, if the source provides a string like "123" but the target expects an integer, this mismatch will trigger the error.

  2. Use Data Type Conversion:

    • Convert the source data to the correct type before mapping it to the target.

    • Use a type conversion function like:

      • TO_INT to convert a string (e.g., "123") to an integer.

      • TO_CHAR to convert numbers (e.g., 123) to a string.

      • TO_DATE to convert a string (e.g., "2024-07-24") to a date.

  • Alternative Solution:

    • Adjust the Target Column:
      If possible, modify the target column’s data type to match the source data. This is useful when the source data is correct, but the target column is too restrictive.

Error : Value Too Long for Type STRING

  • Error Message:

Error: 'Cannot perform operation: Caused by: org.postgresql.util.PSQLException: ERROR: value too long for type STRING'

  • Description:
    This error occurs when you're trying to insert or map a value that is longer than the allowed character length of the target column.

  • Solution:

  1. Check the Length of the Source Data:

    • Examine the source data to identify if any values are longer than the allowed length of X characters.

    • For example, if the target column only allows X characters but the source data contains a string like "example" which is longer than X characters, this will trigger the error.

  2. Adjust the Target Column (If Possible):

    • If truncating the data isn’t ideal, consider increasing the size of the target column to allow for longer values.

    • For example, change the column from STRING(X) to STRING(Y) where Y is greater than X if the data frequently exceeds X characters.

Error : Job Failed for Agent DESKTOP-1XXXXXM9(When mapping is executed using Secure Agent)

  • Error Message:

Could not execute job. Please make sure the agent is up and running

  • Description:
    The job failed to execute because the specified agent was either not running or not available. This can happen for several reasons, such as the agent being stopped, experiencing network issues, or encountering other operational problems.

  • Solution:

  1. Verify Agent Status:

    • Check if the agent DESKTOP-1OOPEM9 is currently running. Ensure that the agent service or application is active on the machine.

  2. Restart the Agent:

    • If the agent is not running, start or restart the agent service. This may involve accessing the machine where the agent is installed and ensuring that the agent process is active.

  3. Check Network Connectivity:

    • Ensure that there are no network issues preventing the agent from communicating with the server or job scheduler. Verify network connections and firewall settings if applicable.

  4. Review Agent Logs:

    • Look at the agent’s log files for any error messages or issues that could explain why it is not running or is unable to execute jobs.

  5. Verify Job Configuration:

    • Ensure that the job configuration is correct and that it is properly assigned to the DESKTOP-1OOPEM9 agent.

Error: Cannot Perform Operation - NULL Result in a Non-Nullable Column

  • Error Message:
    NULL result in a non-nullable column or ERROR: null value in column "customer_id" violates not-null constraint .

  • Description:
    This error occurs when you try to insert or update a record with a NULL value in a column that is defined as NOT NULL in target. The database enforces data integrity rules by ensuring that non-nullable columns always have a valid value.

  • Solution:

  1. Check Column Definition:

    • Verify the column definition in your target table schema to confirm that it is set as NOT NULL.

  2. Validate Data Source:

    • Ensure that the data being inserted or updated does not contain NULL values for columns that are defined as NOT NULL. Check your source data or transformation logic for any missing values.

  3. Modify Data Transformation:

    • If your source data contains NULL values, you might need to handle these values before inserting them into the table. Consider using default values or conditional logic to provide valid data for non-nullable columns in target.

  4. Update Table Schema (If Applicable):

    • If it’s acceptable for the column to have NULL values, you can modify the target table schema to allow NULL values by altering the column definition.

Error: Unable to Generate Data

  • Error Message:
    Unable to generate data

  • Description:
    This error can occur if the source connection has no data available or if there are issues with the connection configuration.

  • Solution:

  1. Verify Data Presence:

    • Ensure that the source connection actually contains data. Double-check the source system or API to confirm that the data is available and accessible.

  2. Check Source Connection Details:

    • Verify the connection configuration, including credentials, endpoint URLs, and any other relevant settings. Ensure that the connection is properly set up and can access the intended data source.

  3. Test the Connection:

    • Use any available tools or features to test the connection to ensure it is working as expected. Some platforms provide a "Test Connection" button or similar feature.

  4. Create a New Connection:

    • If the connection details are correct but the issue persists, consider creating a new connection from scratch. This can help resolve issues related to connection configuration or corruption.

  5. Review API or Source System Limits:

    • Check if there are any limitations or restrictions imposed by the API or source system that might affect data retrieval.

Error: Invalid Length of Timestamp String

  • Error Message:

Invalid length of timestamp string 1701774616000: 13. Microseconds should be 16 digits.

  • Description:
    This error occurs when the system expects a timestamp with microseconds (16 digits), but a timestamp with milliseconds (13 digits) is provided.

  • Solution:

  1. Verify Correct Date Format Handling:

    • Ensure that the correct date format is used considering the length of timestamp. EazyDI provides multiple date formats along with this three formats - (Epoch timestamp in milliseconds-13 digits, Epoch timestamp in milliseconds-16 digits, Epoch timestamp in milliseconds-10 digits).

Error: Duplicate Key Value Violates Unique Constraint

  • Error Message:
    Cannot perform operation: Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "dummy_table_pkey" Detail: Key (id)=(1) already exists

  • Description:
    This error happens when you try to insert or update a row with a key that already exists in a column with a unique constraint

  • Solution:

  1. Check Existing Data:

    • Before inserting a new record, verify whether the value for the unique column already exists in the table.

  2. Use a Unique Sequence for Primary Keys:

    • If you want to avoid inserting duplicate records, ensure that the new values are unique. For example, you can manually assign a new id or rely on an auto-incrementing sequence for primary keys.

Error(Salesforce): Insufficient Access or Missing Cross-Reference Object in Salesforce

  • Error Message:
    Cannot perform operation: Not all records were successfully saved. [{'statusCode': 'INSUFFICIENT_ACCESS_ON_CROSS_REFERENCE_ENTITY', 'message': 'insufficient access rights on cross-reference id: 0014K00000KimXr', 'fields': []}]

  • Description:
    This error occurs when you're trying to insert or update a Salesforce record that references another object or record. The issue arises due to one of the following reasons:

    • Insufficient access rights to the related object.

    • The related object or record is missing, either because it does not exist or has been deleted.

    • Non-existent or Deleted Record: The referenced record (cross-reference id: 0014K00000KimXr) might no longer exist in Salesforce. For instance, if you are inserting or updating a record that references an Account, Contact, or other related object, and that record has been deleted or never existed, the system cannot complete the operation.

  • Solution:

  1. Check for Missing or Deleted Records in Salesforce:

    • Verify that the referenced object (e.g., Account, Contact, etc.) and the specific record with ID 0014K00000KimXr exists in Salesforce.

    • Use Salesforce’s Global Search to check whether the record still exists or has been deleted.

    • If the record has been deleted, either restore it from the Recycle Bin or update the reference in your operation to point to an existing record.

  2. Handle Null or Invalid References in Salesforce:

    • Ensure that the cross-reference id is valid and present in Salesforce.

    • If the related object or record is not essential for the operation, modify your code or mapping logic to handle null or invalid references gracefully. This could involve checking for the existence of the related object before attempting to insert or update.

  3. Check User Permissions in Salesforce:

    • If the record exists, but the user performing the operation does not have sufficient permissions, ensure the user has the required access. Check user permissions, sharing settings, and field-level security as outlined in the previous solution.

  4. Cross-Reference Integrity:

    • Make sure the data you are inserting or updating is consistent with the relationships in Salesforce. For example, when inserting a record, verify that the referenced records (like Account or Contact IDs) are correct and belong to valid, existing objects.

Error(Salesforce): Cannot Specify Id in an Insert Call of Salesforce

  • Error Message:
    Cannot perform operation: Not all records were successfully saved. [{'statusCode': 'INVALID_FIELD_FOR_INSERT_UPDATE', 'message': 'cannot specify Id in an insert call', 'fields': ['Id']}]

  • Description:
    This error occurs because the Id field, which is automatically generated by Salesforce for new records, is being included in an insert operation. Salesforce does not allow you to specify an Id during an insert call because it generates one for you upon successful insertion of the record.

  • Solution:

  1. Remove the Id Field(in field mapping page) from Insert Operation:

    • When inserting a new record, do not pass the Id field in the data being sent to Salesforce. The Id is only used for update or upsert operations, where an existing record is referenced.

    • Ensure your mapping or data operation only includes fields that are valid for insert.

Error(Salesforce): Field-Level Security Issue

  • Error Message:
    Cannot perform operation: Not all records were successfully saved. [{'statusCode': 'INVALID_FIELD_FOR_INSERT_UPDATE', 'message': 'Unable to create/update fields: Name. Please check the security settings of this field and verify that it is read/write for your profile or permission set.']

  • Description:
    This error occurs when the Salesforce user or process does not have the necessary permissions to create or update the Name field for the records being inserted or updated. The field-level security settings might restrict access to this field for the user profile or permission set associated with the operation.

  • Solution:

  1. Verify Field-Level Security in Salesforce:

    • Ensure that the Name field(this can be any other field) in salesforce is set to be read/write for the user profile or permission set performing the operation.

  2. Check Profile or Permission Set in Salesforce:

    • Make sure the user profile or permission set used for the operation has the necessary permissions to create and update records.

  3. Review Object Permissions in Salesforce:

    • Confirm that the user profile or permission set has appropriate object-level permissions for the object containing the Name field.

  4. Field Accessibility Settings in Salesforce:

    • Ensure that the Name field is not set to be Read-Only due to field accessibility settings.

  5. System Admin Debugging in Salesforce:

    • If the issue persists, contact with your System Administrator so that he/she can test the operation to verify if the issue is specific to the user or if it’s a broader configuration issue.

    • If the admin can perform the operation without issues, it indicates a permissions problem specific to the original user.

Error(Salesforce): Duplicates Detected

  • Error Message:
    Cannot perform operation: Not all records were successfully saved. [{'statusCode': 'DUPLICATES_DETECTED', 'message': 'Use one of these records?', 'fields': []}]

  • Description:
    This error occurs when Salesforce detects that the records being inserted or updated match existing records based on the criteria defined in a duplicate rule.

  • Solution:

  1. Review Duplicate Rules:

    • Check the duplicate rules configured in Salesforce to understand the criteria that are causing the duplicates to be detected.

  2. Examine Duplicate Records:

    • Investigate the existing records in Salesforce that are flagged as duplicates. Determine if the records already exist and if they are indeed duplicates of the records you are trying to insert or update.

Error(Exchange Rates): Cannot Perform Operation - Access Restricted

Error Message:
function_access_restricted, Access Restricted - Your current Subscription Plan does not support this API Function.

Description:
This error occurs when attempting to use an API function or feature that is not available under your current subscription plan. In this scenario, it pertains to using exchangerates as a source in your mapping.

Solution:

  1. Check Subscription Plan:

    • Review your current subscription plan to confirm what features and API functions are included. This information is typically available in your account settings or through the service provider’s documentation.

  2. Upgrade Subscription Plan:

    • If the feature you need is not included in your current plan, consider upgrading to a higher tier that includes the required API function or feature.