Convert string to DateTime in SSIS

Question

The data from source has string data type and sometime has invalid dates, empty column ('') I want to check whether it is valid date and convert string to date time. How can i do this in SSIS?

My string value is already formatted as mm/dd/yyyy.

Solution 1

What datetime value should an empty string be transformed into? NULL? You could use a derived columns transformation and / or script transformation.

Solution 2

Here is an expression that you can use in a Derived Column Transformation that will convert the date to a DT_DBTIMESTAMP data type. Or, it will set the value to null (with DT_DBTIMESTAMP data type) if it is null or blank. The DT_DBTIMESTAMP data type matches the SQL Server DATETIME data type.

ISNULL(MyDate) || TRIM(MyDate) == "" ? NULL(DT_DBDATE) : (DT_DBDATE)MyDate

If the input value is not a valid date you can map the error output path to a another Derived Column Transformation that assigns a null to the column.