SQL Server 2005 import wizard and null values

Question

I have a .csv file (semicolon separated) that I am trying to import into an SQL Server 2005 database using the Import and Export Wizard.

Some of the entries in the .csv file are blank (i.e. two separators with no characters in between), and these should translate to NULL values in the destination table. The relevant fields are nullable.

However, the wizard fails on these entries:

"Error 0xc020901c: Data Flow Task: There was an error with input column [field_name] on input "Destination Input" (71). The column status returned was: "The value could not be converted because of a potential loss of data.". (SQL Server Import and Export Wizard)"

I'm pretty sure this worked ok under SQL Server 2000.

I have used sed to replace the blank entries in the file with NULL and null to see if this would solve things, but to no avail.

Is there any way to solve this without resorting to creating and manipulating an SSIS package? I'm hoping to proceduralise this for other users who won't have any SSIS knowledge, and the import/export wizard is far simpler.

Thanks

Answer 1

I don't think the problem is the blank values. More likely you have values in the input column that exceed the storage capacity of the destination column - for instance a value that exceeds a signed integer if your destination column is defined as Integer.

Dosn't the import/export tool report the number of the row that triggered the error somewhere?