3

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?

Improve this question
2
  • column type : datetime
    – gdw
    Jul 11, 2017 at 19:37
  • Can you manually change the value to "5/19/2017 17:22:23.777" and try importing again? I seem to remember the AM/PM throwing DT_DATE off. Your original value casts fine as a Datetime (SELECT CAST('5/19/2017 05:22:23.777 PM' AS DATETIME) ) but the DT_DATE is a little special. If not, then try putting it into a STRING column first, then update the date column to the string column's value.
    – SQLDevDBA
    Jul 11, 2017 at 19:49

2 Answers 2

Reset to default
1

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.

Improve this answer
4
  • I tried that and got the same error. Error " Invalid Date Format"
    – gdw
    Jul 12, 2017 at 18:25
  • @gdw Did you also try the DT_DBTIMESTAMP type? It's lower precision which may eliminate your error.
    – John Eisbrener
    Jul 12, 2017 at 18:33
  • I need the result from this format "5/24/2017 05:12:44.243 PM" to this "2017-05-24 17:12:44.243". I tried using this derived column expression "(DT_DBTIMESTAMP)(SUBSTRING(EventDate,FINDSTRING(EventDate,"/",2) + 1,4) + "-" + SUBSTRING(EventDate,1,FINDSTRING(EventDate,"/",1) - 1) + "-" + SUBSTRING(EventDate,FINDSTRING(EventDate,"/",1) + 1,FINDSTRING(EventDate,"/",2) - FINDSTRING(EventDate,"/",1) - 1) + " " + SUBSTRING(EventDate,FINDSTRING(EventDate," ",1) + 1,8))" but I am getting the result in this format "2017-05-22 05:21:00.000" also this is not converting the 12 hour to 24 hour.
    – gdw
    Jul 12, 2017 at 21:48
  • I tried using the DT_DBTIMESTAMP but still I am getting the error
    – gdw
    Jul 12, 2017 at 21:52
1

I'm sure this has been solved long ago, but I was just running into this issue myself. My solution, was to edit the source file (.csv) and replace the word NULL with blanks.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.