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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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:
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]


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
richxyzAuthor Commented:
I figured it out
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.