SQL Server import - smalldatetime error

Question

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?

Does your table column TenancyStartDate contains NULL and does your table column accept NULL? — President Camacho, Feb 09 '16 at 15:16
It does not accept NULL but I am not trying to give it NULL in the CSV. — tomstephens89, Feb 09 '16 at 15:17
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, Feb 09 '16 at 15:18
That would require me to write some SQL script to do the cleaning and is more long winded than I want this to be. Its a simple one off import of data into an existing table. And no, there is only one row of data in my test CSV. — tomstephens89, Feb 09 '16 at 15:19
You're sure there's not blank lines at the end of the CSV, or anything like that? — Matt Gibson, Feb 09 '16 at 15:20
And you're matching the right columns with each other. Check for tabs in the excel sheet so you don't import the wrong one. — President Camacho, Feb 09 '16 at 15:21
Yeah. I don't know much about the import/export wizard, but if you can, I'd allow NULLs on that column temporarily and see if the import works; if it does you should be able to figure out from the results what the problem is. — Matt Gibson, Feb 09 '16 at 15:27
@MattGibson thanks, it was some blank rows at the bottom of the CSV! — tomstephens89, Feb 09 '16 at 15:58

score 2 · Accepted Answer · answered Feb 09 '16 at 16:03

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

SQL Server import - smalldatetime error

1 Answers1