Date time data conversion failure

• cstg85

  Ten Centuries

  Points: 1264

  More actions

  September 16, 2014 at 11:48 am

  #311620

  I am working on a project to pull data into SQL Server from flat text files. The issue I am having is with the date and time columns. In SQL Server I setup the table to use a datetime data type. Now when I try to insert the date and time into the database via a SSIS package it spits out an error that states: "Data conversion failed for column "Column 12" returned status value 2 and status text "The value could not be converted because of a potential loss of data.""

  So I guess my questions are;

  1) Do I actually need a date conversion even though the date I am inserting is created in the exact format the database stores it in?

  2) What DataType should I be using in the flat file connection manager for that date? (Maybe I am not using the correct one SSIS data type..)

  Here is an example of the date I am inserting: 2014-09-11 15:11:17

  Thanks!

• Eirikur Eiriksson

  SSC Guru

  Points: 182947

  More actions

  September 16, 2014 at 2:16 pm

  #1745912

  cstg85 (9/16/2014)

  ---

  I am working on a project to pull data into SQL Server from flat text files. The issue I am having is with the date and time columns. In SQL Server I setup the table to use a datetime data type. Now when I try to insert the date and time into the database via a SSIS package it spits out an error that states: "Data conversion failed for column "Column 12" returned status value 2 and status text "The value could not be converted because of a potential loss of data.""

  So I guess my questions are;

  1) Do I actually need a date conversion even though the date I am inserting is created in the exact format the database stores it in?

  2) What DataType should I be using in the flat file connection manager for that date? (Maybe I am not using the correct one SSIS data type..)

  Here is an example of the date I am inserting: 2014-09-11 15:11:17

  Thanks!

  1) Under normal circumstances, No, belt and braces approach would be yes.

  2) For dates later than 30 December 1899 use DB_DATE

  You'll find an overview here Integration Services Data Types

  

• cstg85

  Ten Centuries

  Points: 1264

  More actions

  September 17, 2014 at 2:42 pm

  #1746222

  Eirikur, thank you for the response. At first it wouldn't pull in the time with the date, that was actually caused by the flat file souce, it was set to DT_DBDATE where the convert step was set to DT_DBTIMESTAMP. Once I changed the flat file source data type everything worked smoothly.

  Thanks again!