import date format from excel to MS SQL Server Mgmt using wizard

Question

I'm trying to import a table with a date column from excel to MS SQL database using the Server Import and Export Wizard.

The data type of the column in the database is 'datetime'. I've tried formatting the corresponding excel column as 'short date', then as 'text': "19-02-2015" and after that as 'custom date' : "19/02/2015 00:00", but I consistently get the following error message:

Error 0xc020901c: Data Flow Task 1: There was an error with Destination - Share_class_data.Inputs[Destination Input].Columns[Valuation_date] on Destination - Share_class_data.Inputs[Destination Input]. The column status returned was: "The value violated the integrity constraints for the column.". (SQL Server Import and Export Wizard)

Which data type will marry up with a date format from excel?

Or is the only alterantive to change the data type of the column to varchar, import a date column formatted to text and then convert that to a second date column with date data-type?

Answer 1

You need to check the constraints on the column you have the problem with. One of the values violate one of the constraints.

Example: If your column is not nullable, but you have a record in your excel file which does not contain a value, then your process will try to insert a record with null as a value for a not nullable field, resulting in this integrity error.