I'm getting the following error when I run my package:
[Data Conversion [2]] Error: Data conversion failed while converting column "FieldName" (373) to column "Copy of FieldName" (110).
The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
However, I don't understand why. I have double checked the inputs and outputs to validate that they make sense and are what I expect. I've also checked all the raw data in the column in my excel file.
My package setup: Excel Datasource feeding Data Conversion then Derived Column and finally output to Ole DB Destination (sql)
What I've done:
I opened the advanced editor on the data conversion. I confirmed that the incoming data type is DT_STR which can be expected since the source datatype wasn't correctly identified. It is actually a date in my excel file. I confirmed that the data conversion output column is database timestamp [DT_DBTIMESTAMP] as I have set it to be. My destination table has a DateTime datatype for FieldName.
What am I missing?
I think this is a date format issue, check that column does not contains empty strings or NULL values.
Also check that values are similar to yyyy-MM-dd HH:mm:ss date format.
To read more about SSIS data types check the following article:
Also when converting string values to datetime, if values are well formated, just map the source column to the destination without Data conversion Transformation and they will be implicitly converted