I have an SSIS conversion and am trying to convert dates from an Oracle datasource that are formated YYYYMMDD HH:MM:SS.00 I worked through the Unicode to DT_STR conversion but now can't seem to handle NULLs in the data.
My first derived column replaces the DetailActivityStartDate column which is string (DT_STR) using the following expression:
(DetailActivityStartDate == "") || (ISNULL(DetailActivityStartDate)) ? "" : SUBSTRING(DetailActivityStartDate,1,4) + "-" + SUBSTRING(DetailActivityStartDate,5,2) + "-" + SUBSTRING(DetailActivityStartDate,7,2) + " " + SUBSTRING(DetailActivityStartDate,10,2) + ":" + SUBSTRING(DetailActivityStartDate,12,2) + ":" + SUBSTRING(DetailActivityStartDate,14,2)
Then I have added another Derived Column task because I was getting errors from null values when trying to just convert to database timestamp (the destination is a datetime column in SQL 2008.) Here is the expression (that is failing) of the Derived Column task that also converts the string from the task above, to database timestamp:
(DT_DetailActivityStartDate == "") || (ISNULL(DT_DetailActivityStartDate)) ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)DT_DetailActivityStartDate
The error I am getting are:
[Derived Column - Date Nulls ] Error: The conditional operation failed.
[Derived Column - Date Nulls ] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column - Date Nulls" (1513)" failed because error code 0xC0049063 occurred, and the error row disposition on "output column "DetAct Derived Column 3" (2348)" 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 - Date Nulls" (1513) failed with error code 0xC0209029 while processing input "Derived Column Input" (1514). 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.