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
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).
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
Then I redid the import data process, and the rows were successfully transferred.
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)
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.
User contributions licensed under CC BY-SA 3.0