SQL Server import - smalldatetime error

0
votes

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?

1
sqlsql-servercsvimportsmalldatetime
Does your table column TenancyStartDate contains NULL and does your table column accept NULL? - President Camacho
It does not accept NULL but I am not trying to give it NULL in the CSV. - tomstephens89
You could utilize a staging table where you bring in all data as VARCHAR and then clean the data and insert it into the target table. - Fuzzy
Does it reads any rows at all before the error? - President Camacho
You're sure there's not blank lines at the end of the CSV, or anything like that? - Matt Gibson

1 Answers

2
votes

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.)