[Derived Column [9908]] Error: An error occurred while attempting to perform a type cast.

[Derived Column [9908]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (9908)" failed because...

I did try all the steps which you have mentioned along with changing the data type to all the datatype options present in the text file using the advanced editior...

Check this link.. it should help you

http://biresort.net/blogs/pedrocgd/archive/2009/08/06/stepbystep-ssis-extract-data-from-multiple-excel-worksheets.aspx

but i am writing a function