Q. I'm trying to import a CSV file into a SQL Server table using DTSWizard, but I get data truncation errors. Why?

A. DTSWizard is a great tool for importing data into SQL Server, including importing from a CSV file. (CSV files can be created easily in applications such as Excel.) However, I recently tried to import data but kept getting data truncation errors, even though my SQL tables destination columns had plenty of room.

By default, when you import from CSV, every column for the source is set to varchar[50], which means any value longer than 50 characters will be truncated—thus the error. The solution is to modify the source column properties, which you do in the Choose a Data Source page of the wizard. Select the Advanced options. For data types, select Suggest Types... and you can enter several columns that the wizard should check to get an idea of the data stored in the file. The wizard will recommend data types and sizes for all the columns that will better match the inbound data and avoid any truncation. Make sure you select enough rows to ensure the full array of data in the columns is seen by the wizard (to make sure the right data types and sizes are chosen). Below is an example of the recommendation for my data.

When you perform this suggested sizing later in the wizard when it creates the target SQL table it will use the same sizes and data types automatically.

See 18:30 in the video for a full demo of this in action.