"Invalid date format" when importing flat file with Import Wizard

Question

Good afternoon,

I have a dilemma that I cannot figure out. I am trying to import a flat file in a SQL db table but am having issues. My column in SQL is a date time column for date of birth DOB. The extraction flat file provided to me has this column as date...thus when I importin to SQL I am getting:

Messages
Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Invalid date format".
 (SQL Server Import and Export Wizard)

Error 0xc020901c: Data Flow Task 1: There was an error with input column "DOB" (212) on input "Destination Input" (147). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
 (SQL Server Import and Export Wizard)

Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "input "Destination Input" (147)" failed because error code 0xC020907A occurred, and the error row disposition on "input "Destination Input" (147)" 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.
 (SQL Server Import and Export Wizard)

I am trying to figure out how to add a random time like 00:00:00.000 to every DOB in the flat file. example, they send me 1983-11-30 but I want to use 1983-11-30 00:00:00.000

Is there a way to do this or does anyone have an idea of what I can do? Thanks in advance

Solution 1

My situation was a little different but I feel posting about it could help others out there. Unlike OP, I was importing data from an Access 2000 mde file into sql server management studio, but similar to OP I ran into the "Invalid data format" error. I knew the conflict was caused by the fact that the columns of type DateTime in the access file conflicted with the DataTime format in sql.

Solution: After I went through the import data process, which is done by right clicking the server name in sql server management studio -> Tasks -> Import Data and then choosing the source (access), destination (sql server), and table to be imported, I noticed that 0 rows were transferred because of the type issue. However, a table was created for me in SQL Server Management Studio that represented the table I was trying to import from the access table. It had the right columns and their associated data types.

So what I did was I right clicked the table in order to go into design view and changed all the data types of the DataTime columns to just Date,

Then I redid the import data process, and the rows were successfully transferred.

Solution 2

MSSQL will have no problem converting a yyyy-MM-dd string like yours to a date. You'll end up with 00:00:00.000 automatically. The error message you posted "Conversion failed because the data value overflowed the specified type" indicates that one of the dates is outside the valid range. You most likely got an invalid date of birth (look for a 0000-00-00 record or similar). If the flat file is delimited in a way Excel can easily parse, pull it into Excel and sort that column. Look at both the smallest and largest values and you'll likely find the offending record(s).

Solution 3

I changed the type of row from datetime to date (in the mapping) while importing the data via Wizard and it worked for me.

Comments

[Lamak]

This shouldn't be a problem (at least, not the one you think). A DATETIME column will automatically assume that the time is 00:00:00.000 when it only receives a date. The most likely issue here is whit the date format, SQL Server may be assuming that the format for your date is YYYY-DD-MM instead of YYYY-MM-DD

[Joachim Isaksson]

Try doing SET DATEFORMAT ymd before running the bulk insert.

[Robby Johnston]

Thank you....I think my issues is elsewhere...I ran just one through with the format staying the same and it assuming 0's like you thought. Thank you

[Robby Johnston]

Thanks....doing that now. I appreciate it. I did find 151 occurrences of what you were stating. It is still happening though so I am sure something else is in there. I am bringing the file now from the server to my computer since server doesnt have excel. There are 1.6 millions rows in it