Hi Smith,

I added a derived column transformation and added the expression as per your advise as below:-

"ISNULL(Date) ? NULL(DT_DATE) : (DT_DATE)(SUBSTRING(Date,4,2) + "/" + SUBSTRING(Date,1,2) + "/" + SUBSTRING(Date,7,2))"

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.

In Source Table [Staging ], Date column has a varchar(50) data type

In Destination Table, Date column has datetime data type.

Is there any other setting that I am missing ?