It appears that there is a bug with the Data Import Wizard in both TDP 3.8 and TDP 4.0.
Source: Excel 2013 File (.xlsx)
Destination: SQL Server 2008 R2
When attempting to import a file containing a date of ‘0001-01-01’ into a table where the destination column is of the datatype DATETIME2(7), TDP gives the following error message:
“Row 1 failed to insert, error: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.”
It appears that TDP is assuming that the DATETIME2(7) datatype is actually just DATETIME - which does only support a date going back to 1753-01-01.
However, we specifically use DATETIME2(7) on all our date columns, and if I manually insert the row, there is no issue - so it’s definitely a problem with the Import Wizard.
I’ve reproduced the issue on both TDP 3.8 and TDP 4.0.