SQL Server 2005 import wizard and null values

2
votes

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

1
sql-server-2005ssis
First question is what source and destination connections are you using? OLEDB? - Frozenskys
Source is 'flat file source', destination is 'SQL Native Client' - meepmeep
I have the same problem. Sune's answer below doesn't help, because I have checked each value and it doesn't exceed the data type range. Did you ever solve this? - Ronnie Overby
Sadly there was no solution - but I have since upgraded to the 2008 client tools which resolves this by having explicit null handling options. - meepmeep

1 Answers

0
votes

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?