Error adding new column SSIS 2019 - MS Oracle Source connector

Question

Hi everyone,

I would like some help, I'm creating a basic flow in SSIS 2019 (I simplified it as below to help about the problem).
My Data source is from Oracle Database (I'm using MS ORACLE Source connector).

The flow is working:

When I'm adding a new column ("column_name"), as image below:

I'm receiving an error:

Error:

• [Oracle XE [12]] Error: SQLSTATE: 22018, Message: [Microsoft][ODBC Oracle Wire Protocol driver]Invalid character value. Error in column 3.;
• [Oracle XE [12]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Oracle XE.Outputs[Oracle Source Output]" failed because error code 0xC020F450 occurred, and the error row disposition on "Oracle XE" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
• [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Oracle XE returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

Could someone help?

Thanks in advance!

Alex

Answer 1

Hi @Alexandre Rosas Brandao ,

1.Please delete the old Oracle Source and then create a new Oracle Source after your adding a new column ("column_name").
Could you please share the screenshot about the preview page in Oracle Source?

2.Please set ValidateExternalMetadata as False.

Best regards,
Mona

---

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

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi Mona,

First, thank you so much for your help! You gave me a good idea!

For sure!

This picture is before the problem, when the flow worked correctly.

I noticed that the problem happened when I added a new column ("column_name") in the middle of "old columns" (table_name, data_type, data_length, column_id) that I mapped before.

If I remove this "column_name" column and tried again or if I put this new column in the last column of my SELECT, it also works, look:

For me, looks like a bug from SSIS 2019. What do you think?

Great job!

Thanks for your help,
Alex

Answer 3

Was anything additional figured out with this issue? I had a similar issue where I updated some SSIS packages from SSIS 2012 to 2019. After I performed the update, one of my packages was encountering the same error. I tried playing with some column ordering, as well as re-creating the data source in the same data flow, and neither resolved the issue.

In the end, I created a new Data Flow Task in the same package, created a new Oracle Data Source in the new data flow, and then copied & pasted all the transforms from the old data flow to the new one. Linked the new data source to the copied transforms. Then disabled the old data flow. After that, things worked. Don't know how or why.