Share via

SSIS: DTS_E_INDUCEDTRANSFORMFAILUREONERROR and ORA-01722 due to string column

Anshul Rawat 0 Reputation points
2023-07-20T03:45:08.8866667+00:00

Hello,

In a data flow task connected to an Oracle DB, we are getting ORA-01722 due to a string column. However, this error is supposed to appear when unsuccessfully converting string type to numeric type but there are no such operations happening. The column in the source is varchar, it is imported as DT_STR in SSIS and not transformed into numeric value at any point.

What's even more confusing is that this issue seems to be appearing randomly. Rarely, the pipeline would work with no errors even when no changes are made to the data or the task.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,642 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Amira Bedhiafi 28,381 Reputation points
    2023-07-20T13:08:41.5166667+00:00

    Given the random nature of the error and the fact that no changes are made to the data or the task, the issue may lie with the data itself or some specific records within the column causing the error. Here are a few potential reasons for this behavior:

    Data Variability: There might be some data variability in the varchar column that intermittently causes issues during data processing. For example, some records might contain non-numeric characters that are not immediately apparent, leading to the error.

    NLS Settings: The behavior of implicit type conversions in Oracle can be affected by NLS (National Language Support) settings. Depending on the session's NLS settings, certain characters or numeric formats might be interpreted differently, potentially leading to the error.

    Data Source Changes: Even though you mentioned no changes were made, it's possible that the data source (Oracle DB) had some changes that were not accounted for in the data flow task, such as triggers, constraints, or dependent data changes.

    Data Source Changes: Even though you mentioned no changes were made, it's possible that the data source (Oracle DB) had some changes that were not accounted for in the data flow task, such as triggers, constraints, or dependent data changes.

    1 person found this answer helpful.
    0 comments
  2. ZoeHui-MSFT 40,431 Reputation points
    2023-07-21T05:33:32.27+00:00

    Hi @Anshul Rawat,

    You may configure the error output row to check the data which caused the error.

    And then you could narrow down the issue to see if there is any setup in your data from Oracle.

    Check: Add error flow redirection

    Regards，

    Zoe Hui

    If the answer is helpful, please click "Accept Answer" and upvote it.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.