SSIS Convert different Date String format to Date

Question

I am trying to extract Data from Excel to SQLDB using SSIS ETL.

The Excel sheet contain a column called "Date". The issue is that the date in this column come as string in different formats:

01.01.2016
1-Jan-2016
1-Jan-2016
01.01.2016

how to convert it in SSIS to one Date format

Are you just using the import / export wizard or have you written it from scratch? Sometimes the wizard will do things it thinks might be correct for odd reasons, have a look into the specifics of what the package is doing. — Tim Edwards

Paras Sharma Paras Sharma · Accepted Answer · 2016-08-11T06:53:11

You can use Derived Column task in data flow to cast the [date] column of your excel to date time datatype.