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.

  • 1
    You have fields in a CSV that exceed 2GB in size? Really? I mean...really? – Mark Sinkinson Aug 11 '14 at 14:35
  • do you know how to get the import wizard to skip failed rows instead of aborting? – jimmyjambles Aug 11 '14 at 14:41
  • 1
    Not possible with the Import/Export Wizard. It's expecting a valid file. You'd need to use SSIS – Mark Sinkinson Aug 11 '14 at 14:46
  • 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. – Mark Sinkinson Aug 11 '14 at 14:47
  • SSIS error when loading data from flat files will help you. – Kin Shah Aug 11 '14 at 15:02

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.