Derived column error when using datetime fields

Question

Am trying to do something like this.

[Part Time Zone] == "CST" ? DATEADD("hh",1,(DT_DBTIME)[Part Start Date]) : 
([Part Time Zone] == "MST" ? 
DATEADD("hh",2,(DT_DBTIME)[Part Start Date]) : DATEADD("hh",3,(DT_DBTIME)
[Part Time Zone]))

where [Part Time Zone] is String field.

Getting the following error

[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.Inputs[Derived Column Input].Columns[Part Start Date]" 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_PROCESSINPUTFAILED. The ProcessInput method on component "Derived Column" (2) failed with error code 0xC0209029 while processing input "Derived Column Input" (3). 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.

steenbergh steenbergh · Accepted Answer · 2018-03-13T14:52:10

It's probably a problem with the data in your casts to DT_DBTIME. Could you perhaps put a Derived Column transformation (DCT) before this one where the field [Part Time Zone] is cast to Time (you could name it [Part Time Zone as Time], for instance), and then use that new colum in this Derived Column Transformation.

That way, you can configure the first DCT to redirect rows to the error output. Attach a Recordset destination to that, and slap on a Data Viewer to see what data gives this error.

Derived column error when using datetime fields

1 Answers