SSIS Date Conversion keeps throwing errors String->DBDATE

Question

I'm currently working on a SSIS project where I import from a .csv file, which contains, amongst others, dates. The dates are formatted as yyyy-mm-dd, and also read in as such on the file connection preview. I then do a data conversion where I assign a new column to use the data as DBDATE type. The original data is in WSTR (Unicode String) with size 10. I then get the following errors on my data conversion:

Error: 0xC02020C5 at Data Flow Task, Data Conversion [2]: Data conversion failed while converting column "Leverdatum" (25) to column "NewLeverdatum" (8).  The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

Error: 0xC0209029 at Data Flow Task, Data Conversion [2]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "Data Conversion.Outputs[Data Conversion Output].Columns[NewLeverdatum]" failed because error code 0xC020907F occurred, and the error row disposition on "Data Conversion.Outputs[Data Conversion Output].Columns[NewLeverdatum]" 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.

Error: 0xC0047022 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Data Conversion" (2) failed with error code 0xC0209029 while processing input "Data Conversion Input" (3). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

The project is in locale English(US). I have also tried using the local date format on my machine, which is dd-mm-yyyy. Please advise what I can do to import this column as a date.

I have tried using a Derived Column instead of Data Conversion and I have also tried setting the type of the column to DBDATE directly.

Thanks in advance.

EDIT: Currently it is okay to import the data as a task, since the data will be exported to Excel and this will recognize the date in text form. Would still like an answer if anyone has one

Answer 1

DT_DBDATE format is expecting yyyy-mm-dd

I believe your new column is NewLeverdatum and your old DT_WSTR column is Leverdatum

Based on the error

Error: 0xC02020C5 at Data Flow Task, Data Conversion [2]: Data conversion failed while converting column "Leverdatum" (25) to column "NewLeverdatum" (8). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

it failed because DT_DBDATE will result to 10 character date with dash -

Change your Leverdatum column to allow len = 10