I'm trying to import a .csv file. One column has text from a website comment filed, so there are all kinds of characters entered. Using the Import Wizard, I set the incoming column length to 8000, and the column in my staging table to varchar(max) or nvarchar(max), but I still get these messages. I removed any commas from the file, but there are ampersands and whatnot in that colum. A warning box sometimes comes up when defining the wizard options, and I choose "ignore" for data truncation, but no effect.

Is there a foolproof way to "import everything" into a SQL table ?

Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "Status" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". (SQL Server Import and Export Wizard)

Today it is a one-off. But I will be setting up a weekly process for future imports.I thought I would use the wizard the first time, then save it as a package for future use.

There are probably some funky characters in the text strings that SQL doesn't like.

EDIT: There were some commas in one of the columns that i removed, and in one text box there was 3 pages of diatribe about the economy that had 8,900 characters. I modifed those, and the import worked, but a cumbersome solution.