Why SSIS Date Conversion Failure regardles of validation methods

Question

I have a column in a text-file with dates like this: 20131207. I and trying to convert the date like this but everything I have tried has failed.

Here is my code: I have modified it many times to fix this problem.

(LEN(TRIM(MyDate)) < 8) || (TRIM(MyDate) == " ") || 
(ISNULL(MyDate)) ? NULL(DT_DBDATE) : 
(DT_DBDATE)(SUBSTRING(TRIM(MyDate),1,4) + "-" + 
SUBSTRING(TRIM(MyDate),5,2) + "-" + SUBSTRING(TRIM(MyDate),7,2))

Where am I going wrong?

I checked Retain Null Values in the Flat Source Task.

Update: Here is the error message:

The conditional operation failed.
The "Rename Columns (My_Table)" failed because error code 0xC0049063 occurred, 
and the error row disposition on "Rename Columns (My_Table).Outputs[Derived 
Column Output].Columns[MyDate]" 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.

I need to convert the data in the text file to date, from 20131207 to 2013-12-07 but I want to insert null if the data in the text file is less then 8 in length or cannot be converted to date.

Answer 1

try this:

add a new flat file source then add a derived column then in expression do this

 `LEFT(date,4) + "-" + SUBSTRING(date,5,2) + "-" + RIGHT(date,2)`

the package :

with output like this :

Answer 2

Trim(MyDate)==" " should be Trim(MyDate)==""