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.
Best Answer
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.