SSIS - Standardize date format stored in a text column

Problem:

I have trade_date column which looks like this :

Trade_date
10-02-2012
1-23-2014
feb-14-2016
1/2/2012
01/02/2012
01/01/12
2014/10/26

I want to have one unified format after transformation which is MM/dd/yyyy. Does anyone have this problem before or anyone know how to fix this problem?

Solution:

You need a Script Component to do that, since you are looking to convert multiple date formats. First, you have to create an array of strings that contains all formats needed then you should use DateTime.ParseExact() function

After adding a Script Component, Make sure you add a New Output Column of type string (Or date if you are looking to convert values into date column), then use the following lines of code within the script:

string[] formats = {"dd-MM-yyyy","yyyy-MM-dd","d-M-yyyy","MMM-dd-yyyy","dd/MM/yy","yyyy/MM/dd","dd/MM/yyyy","};
Row.outColumn = DateTime.ParseExact(Row.inColumn,formats,System.Globalization.CultureInfo.InvariantCulture).ToString("MM/dd/yyyy");

Original Post: https://stackoverflow.com/questions/57319115/how-to-convert-mixmatch-date-to-one-uniform-date-in-ssis/57342057#57342057