I have a flat file with datetime format "5/19/2017 05:22:23.777 PM", and when I try to insert this data into a table using SSIS transformation I am getting these errors:
[Flat File Source [2]] Error: Data conversion failed. The data conversion for column ""DT_Date"" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
[Flat File Source [2]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Flat File Source.Outputs[Flat File Source Output].Columns["DT_Date"]" failed because error code 0xC0209084 occurred, and the error row disposition on "Flat File Source.Outputs[Flat File Source Output].Columns["DT_Date"]" 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.
The target column is defined as datetime.
Please help me on this, any help is really appreciated.
How can I solve this issue?
Best Answer
Try adjusting your input column type to
DT_DBTIMESTAMP2. I've seen this fix similarly annoying Date conversion issues. Here's a full list of what those input types mean.