To convert a string to Date, we have to perform the following steps:
- Include a "Data Conversion" Data flow component.
- Right-Click, Display Advanced Editor
- Open Input and Output Properties
- Open the custom properties, fix custom properties to be true.
- Ensure that data type is fixed to database date[DT_DBDATE].
- Run the SSIS Package.