Import errors importing CSV to SQL Server table

Question

I am using SQL Server 2014 Management Studio to import a very large .CSV file into a table.

So far I have been trying to accomplish this by right-clicking on the database and selecting Import Data, then using the wizard to import from a flat file.

The problem I am having is that every time I try to import I either run into truncation errors (some of my fields exceed nvarchar(max)) or that there are missing fields on some rows.

How can I set the import to just ignore a failed row, instead of failing the entire operation. I've looked everywhere in the options but cannot find where to set this.

• Mark Sinkinson: You have fields in a CSV that exceed 2GB in size? Really? I mean...really?
• jimmyjambles: do you know how to get the import wizard to skip failed rows instead of aborting?
• Mark Sinkinson: Not possible with the Import/Export Wizard. It's expecting a valid file. You'd need to use SSIS
• Mark Sinkinson: How are they 'missing fields'? If the fields exist but are empty, you can set the field to 'nullable'. If the file is actually missing fields in a line I.e. There are less commas in some lines, then your file is invalid.
• Kin Shah: SSIS error when loading data from flat files will help you.

Answer 1

At the end of the import/export wizard you have the option to save your SSIS package (yes that's what the wizard uses behind the scenes). Save your package to a file then open it in SQL Server Data Tools. Go into the data flow and open the source. In the "Error Output" tab you will see an option to ignore failure (for truncation specifically or errors in general). Modify it for the columns you are worried about.

All that being said you are probably(99.999%) not seeing a truncation because the column is to big to fit in a varchar(max). What is probably happening is that SSIS "thinks" your column is shorter than it is. If you go into the connection manager for your CSV file you can modify the "width" of your input columns in the advanced tab.