If you didn't have the freedom to modify the data type from varchar to datetime, you could still do something similar, but I absolutely agree with Jeff, try to avoid storing dates as varchars if you have the option.

I get an error output as [Derived Column [610]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (610)" failed because error code 0xC0049063 occurred, and the error row disposition on "input column "Date" (658)" 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.

The expression looks right to me, is there any possibility that the incoming field is not formatted correctly in the source data? The error message seems to indicate a conversion failure, so the string components aren't being reassembled into a valid date. You might consider setting up an exception table, and setting up an error trap to see what the value is. Then you can set the error condition to redirect row instead of fail component. I process a lot of very dirty source data, so all of my packages use a setup like this.

You might also consider throwing a trim call on the inbound varchar prior to substringing it. It can affect performance, but I've had to do it on a few occasions.

It is the source data. There were some datas with dd/mm/yy and some datas were dd/mm/yyyy format. the one with yyyy comes okay after conversion but the one with only yy returns a weird set of result. Example: 18/11/07 becomes 2018/11/07

1. How do I repair the bad format that I am already receiving from the source? Must fix ALL to be yyyy in stead of yy.How can I do this?2. I want it to become american standard, which it should be 11/18/07 and not British standard which is what I am getting now.3. What is the code to do ALL these in a single query so that in SSIS I would save the performance?