3

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?

Community
  • 1
  • 1
mo_maat
  • 2,110
  • 12
  • 44
  • 72

1 Answers1

1

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

Yahfoufi
  • 2,220
  • 1
  • 22
  • 41
Hadi
  • 36,233
  • 13
  • 65
  • 124
  • I already checked, double checked and triple checked. I'm baffled and bit frustrated. It shouldn't be this difficult. I must be missing something simple. I've started from scratch and retried many times. There are some things I could try but if they solve the problem then it would be ridiculous to me. For example, playing with the source excel file and setting the column to ShortDate instead of Date as it currently is. I thought this was the whole point of the Data Conversion transformation. – mo_maat Jan 14 '19 at 23:43
  • Yes, I tried your suggestion to map directly but no luck. For consistency and so I know what to expect I want to explicitly convert and map all my columns. – mo_maat Jan 14 '19 at 23:47
  • @mo_maat try converting to DT_DBDATE then map the column to destination. – Hadi Jan 14 '19 at 23:50
  • This link may give some insights https://stackoverflow.com/questions/43635787/cast-vs-ssis-data-flow-implicit-conversion-difference/43643995#43643995 – Hadi Jan 14 '19 at 23:51
  • In excel date may be stored as date serial – Hadi Jan 14 '19 at 23:51
  • I just test something that might give insight. I imported using the sql import wizard and didn't do any conversions, defaulting to the suggestions from the wizard. The field came in as a float and the value in sql is `43405`. That same record has a value of `11/1/2018` in excel... I guess it is storing the date using the serial number date. I just need to figure out the best way to convert this in ssis. I do not want to do implicit conversions and I want my database date field to be datetime, not date. – mo_maat Jan 15 '19 at 00:02
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/186679/discussion-between-mo-maat-and-hadi). – mo_maat Jan 15 '19 at 00:10
  • http://toddmcdermid.blogspot.com/2009/01/converting-numbers-to-dates-in-derived.html?m=1 – Hadi Jan 15 '19 at 00:14
  • Check `date serials from excel` section – Hadi Jan 15 '19 at 00:15
  • @mo_maat anything new? – Hadi Jan 15 '19 at 23:21
  • 1
    I ended up pre formatting an excel file that has consistent data types. I'm then using it as a template to add data to. Dealing with the possibility of random data types was too much of a headache and not worthwhile. I'll probably tackle that another time for fun and learning. I'm guessing that pasting values from one excel sheet to another will not alter the destination formatting? – mo_maat Jan 16 '19 at 17:28
  • @mo_maat i think that. But be sure to precise the column format. Don't use general data type in excelto avoid that – Hadi Jan 16 '19 at 17:38
  • @mo_maat when the issue is solved accept an answer or write your own. Good luck – Hadi Jan 16 '19 at 17:39
  • I didn't accept yet because I did ask a question and figure I may get answer. I've accepted. Thanks. – mo_maat Jan 16 '19 at 20:11