SSIS Data Conversion Error despite using Data Conversion and accurate destination Datatype

3
votes

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?

1
sql-serverexcelssisetlsql-server-data-tools

1 Answers

1
votes

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