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?

  • column type : datetime – gdw Jul 11 '17 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. – MguerraTorres Jul 11 '17 at 19:49

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 | |
  • I tried that and got the same error. Error " Invalid Date Format" – gdw Jul 12 '17 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 '17 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 '17 at 21:48
  • I tried using the DT_DBTIMESTAMP but still I am getting the error – gdw Jul 12 '17 at 21:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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