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

▲
1
▼

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

sql
sql-server
import
asked on Stack Overflow Feb 1, 2013 by Robby Johnston • edited Feb 1, 2013 by Pondlife

5 Answers

▲
1
▼

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).

answered on Stack Overflow Feb 1, 2013 by Ben • edited Feb 1, 2013 by sharakan
▲
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.

answered on Stack Overflow Jul 20, 2017 by Chris Gong
▲
0
▼

This is old, but I was having a similar issue in my SSIS data flow task and hopefully this might help someone: for me the solution was to check the "Retain null values from the source as null values in the data flow" option in the Flat File Source Editor window (in the Connection Manager page)

answered on Stack Overflow Mar 14, 2015 by LoJo
▲
0
▼

I found that when copying a database from the local EXPRESS to my production server, the table definitions were smalldatetime instead of datetime. In the wizard where you select the tables, you can select Edit Mapping. In there you can change your data type.

answered on Stack Overflow Jan 2, 2018 by Alan Shaw
▲
0
▼

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

answered on Stack Overflow Sep 8, 2020 by JD1933 • edited Sep 9, 2020 by xKobalt

User contributions licensed under CC BY-SA 3.0