SQL Server Error: String or binary would be truncated. (System.Data)

Question

I'm trying to import CSV file into SQL Server via [Tasks]>>[Import Flat File].

I get the error

The given value of type string from the data source cannot be converted to type nvarchar of the specified target column. (System.Data). String or binary would be truncated. (System.Data)

Now I get this error from time to time and I think it's usually associated with large column widths. And normally if it's a small file with small amount of columns I can usually identify the offending column/row.

But for large files with many columns and thousands of rows it is practically impossible. The error inconveniently doesn't tell you what column is affected.

From a best practice point of view is there a way to actually get around this vague error message to pinpoint which is the offending column/row?

Thanks

Answer 1

In task wizard, the data type for strings is chosen nvarchar(50) by default. If you haven't changed the length in the brackets and getting this error, then it means that one or more of your columns have at least one row with value length greater than 50. Calculate the maximum length for all your columns and change the length in brackets for each attributes accordingly.

Performance tip: reducing the variable length where you can will help with your DB performance in the long run.