I am trying to import data into an existing SQL Server table from a CSV file via the built in import/export wizard, however no matter how I format the date/time in the CSV, the wizard throws up an error:
Error 0xc020901c: Data Flow Task 1: There was an error with Destination - Tenant.Inputs[Destination Input].Columns[TenancyStartDate] on Destination - Tenant.Inputs[Destination Input]. The column status returned was: "The value violated the integrity constraints for the column.". (SQL Server Import and Export Wizard)
This is how the existing data actually looks in said column but even if I do a copy and paste of this into the CSV, it still complains!
2007-01-01 00:00:00
Any ideas what I need to do in order to make the import wizard convert my date/time column to small date/time successfully?
Given what you've described, the problem is likely to be null values. If there aren't any that you can obviously see, it's worth checking the very end of the CSV for blank rows. These are easy to overlook and will cause a null to be loaded for each column for each blank row. (I believe you're normally allowed a single carriage return at the end of the last data row.)