Derived column mapping fails

Question

I am mapping data between my oracle table to the Azure SQL DB table. I need to map the name field from the Source which is like below

Name
AX1 BD
A1 12
BC W1

In to source, I need to just map the characters that are before the space like

Dest_Name
AX1
A1
BC

So in the SSIS, I tried to add a derived column between the source and destination

Where in the derived column I am doing like below

And the expression is like

SUBSTRING(NAME,1,FINDSTRING(NAME," ",1) - 1)

But when I am trying to execute the task, I am getting the following error on the Derived Column

Information: 0x4004300C at DF - ORA Room Movement, SSIS.Pipeline: Execute phase is beginning.

Error: 0xC0049067 at DF - ORA Room Movement, Derived Column [133]: An error occurred while evaluating the function

Error: 0xC0209029 at DF - ORA Room Movement, Derived Column [133]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Derived Column" failed because error code 0xC0049067 occurred, and the error row disposition on "Derived Column.Outputs[Derived Column Output].Columns[Trimmed_Name]" 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

Error: 0xC0047022 at DF - ORA Room Movement, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Derived Column" (133) failed with error code 0xC0209029 while processing input "Derived Column Input" (134). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

I am not sure what I am missing here any help is greatly appreciated

Perhaps one of the source columns doesn't contain a space, so `findstring()` and/or `substring()` fail? — Bill Jetzer, Nov 10 '21 at 20:48
*"There may be error messages posted before this with more information about the failure."* We really need those errors. — Thom A, Nov 10 '21 at 20:49
@There is nothing much, I posted all that is relevent to the error message — user4912134, Nov 10 '21 at 20:51
@BillJetzer How can I handle if the field doesnt have the space? — user4912134, Nov 10 '21 at 20:52

score 1 · Answer 1 · answered Nov 10 '21 at 21:07

1

I added a new field on the Oracle Source Query Substr (NAME,1,instr(NAME,' ') - 1) and mapped that to the destination instead of the Derived column

answered Nov 10 '21 at 21:07

user4912134

1,003
5
18
47

Derived column mapping fails

1 Answers1