This is an easy and common problem.

In our dataflow task, you need to add the Derived column transformation connected to the Data Flow Source.

(Between your dataflow source and data flow destination.)

And Either Add a new column, or replace existing. and in the expression column of the Derived column transformation you just need to add a type Cast function in front of the field Name

IE (DT_DBDATE)EVENT_D

Thats It.

Here's a tutorial on how to use the Derived column Transformation.

http://www.dotnetfunda.com/articles/article1233-use-of-derived-column-in-ssis.aspx

Since dates are stored in Text what are the chances that some of the data is not in an actual date?