SSIS 2008 Derived Column Date Conversion

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 [1513]] Error: The conditional operation failed.

[Derived Column - Date Nulls [1513]] 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.

Who is Participating?
richxyzConnect With a Mentor Author Commented:
OK, I figured it out.   I need 3 tasks in my data flow to convert these dates.  Let me know if this resembles your solution - ( or if I'm totally off-base )    but at least this properly converts my date fields that have rows with null values mixed in with them.

1. Derived Column with the following expression.  (DetailActivityStartDate == "") || (ISNULL(DetailActivityStartDate)) ? "1900-01-01" : SUBSTRING(DetailActivityStartDate,1,4) + "-" + SUBSTRING(DetailActivityStartDate,5,2) + "-" + SUBSTRING(DetailActivityStartDate,7,2)

2. Data Conversion converting date to [Copy of DetailActivityStartDate] -> databasea timestamp [DT_DBTIMESTAMP]

3. Derived Column to convert 1/1/1900 back to NULLs with the following expression:  ([Copy of DetailActivityStartDate] == (DT_DBTIMESTAMP)"1900-01-01") ? NULL(DT_DBTIMESTAMP) : [Copy of DetailActivityStartDate]

Alpesh PatelAssistant ConsultantCommented:
Take first Derived columns to convert Null to blank.

and Put condition to check date is blank only.

(DetailActivityStartDate == "") ? "" : SUBSTRING(DetailActivityStartDate,1,4) + "-" + SUBSTRING(DetailActivityStartDate,6,2) + "-" + SUBSTRING(DetailActivityStartDate,9,2) + " " + SUBSTRING(DetailActivityStartDate,10,2) + ":" + SUBSTRING(DetailActivityStartDate,12,2) + ":" + SUBSTRING(DetailActivityStartDate,14,2)

Pleace proper starting point in above example

I am doing same
richxyzAuthor Commented:
I tried your suggestion, and had to add a Data Conversion task afterwards to convert from string to database timestamp (DT_DBTIMESTAMP) - but the date column that contains blanks cause the error below.  I added a viewer and the dates are properly being transformed from "20110301 080600" to "2011-03-01 08:06:00" but the next step when I try to do data conversion to datetimestamp, I get:

[Data Conversion [5273]] Error: Data conversion failed while converting column "DischargeDate" (167) to column "Copy of DischargeDate" (5287).  The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

richxyzAuthor Commented:
I figured it out
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.