I am very new to SSIS, I have existing SQL table and I want to load data into decimal(10,2) column, but .csv file as source file contains -- value in it so that I am trying to replace -- value to 0.00 using derived column and expression
I have changed the Flat File Source output - Output Column to Unicode string [DT_WSTR]
The expression for the new derived column is:
[estimated-order-handling-fee-per-order] == "--" ? (DT_DECIMAL,2)"0.00" : (DT_DECIMAL,2)[estimated-order-handling-fee-per-order]
Derived Column Output - Columns Output is set to decimal [DT_DECIMAL]
SSIS accepts all these, but giving error while running package as below for derived column
[Derived Column [2]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Derived Column" failed because error code 0xC0049063 occurred, and the error row disposition on "Derived Column.Outputs[Derived Column Output].Columns[estimated-order-handling-fee-per-order-derived]" 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.
Can someone help me with this conversion?
Thank you.
I think the error might be caused by empty or null values. Also, try using DT_NUMERIC data type instead of DT_DECIMAL as following:
REPLACE([estimated-order-handling-fee-per-order],"--","") == "" ? (DT_NUMERIC,10,2)"0.00" : (DT_NUMERIC,10,2)[estimated-order-handling-fee-per-order]