CODEWITHSUNDEEP
sqlsql-serversql-import-wizard
SOK
SOK

Reputation: 1792

Date Error when Uploading CSV to table using SQL Server Import Wizard

I have created a table in a database in SQL Server and now am trying to import a CSV to that same table via the import wizard and I am getting the following error:

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 11.0"  Hresult: 0x80004005  Description: "Invalid date format".
 (SQL Server Import and Export Wizard)

The CSV I am trying to upload is the exact same format as the one I used to create the table but seems like something to do with the date is the issue:

The column status returned was: "Conversion failed because the data value overflowed the specified type.". (SQL Server Import and Export Wizard)

The format of the original CSV date column is:

EVENT_DT
31/12/2016 1:40
31/12/2016 2:55
31/12/2016 3:30
31/12/2016 3:30
31/12/2016 3:30

And the CSV im trying to upload looks like:

EVENT_DT
2/04/2020 6:55
2/04/2020 3:50
2/04/2020 4:25
2/04/2020 5:45
2/04/2020 3:15
2/04/2020 3:15

I have tried changing the EVENT_DT data type to database timestamp with precision [DT_DBTIMESTAMP2] when in the Import Qizard but still getting the error.

Can anyone point me to what I'm doing wrong?

Upvotes: 1

Views: 3424

Answers (1)

srvts
srvts

Reputation: 78

Please try to import the flat file data to a staging table (a temporary table) with all the data types as varchar.

Once done, you can convert it to datetime as required while transferring the data to the main table.

Here's how you can convert the string to datetime while inserting data to the main table.

DECLARE @temp1 TABLE (ID INT, EVENT_DT VARCHAR(20))

INSERT INTO @temp1 VALUES (1, '31/12/2016 1:40')
INSERT INTO @temp1 VALUES (2, '31/12/2016 2:55')
INSERT INTO @temp1 VALUES (3, '31/12/2016 3:30')
INSERT INTO @temp1 VALUES (4, '31/12/2016 3:30')
INSERT INTO @temp1 VALUES (5, '31/12/2016 3:30')

DECLARE @temp2 TABLE (ID INT, EVENT_DT DATETIME)

INSERT INTO @temp2
SELECT ID, convert(DATETIME, EVENT_DT, 103) FROM @temp1

SELECT * FROM @temp2

Upvotes: 0

Related Questions