CODEWITHSUNDEEP
sql-serverexcelssisetlsql-server-data-tools
mo_maat
mo_maat

Reputation: 2240

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

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?

Upvotes: 3

Views: 3607

Answers (1)

Hadi
Hadi

Reputation: 37358

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

Upvotes: 1

Related Questions