If you trying to import a CSV/text or Excel file into some table on SQL Server 2000 where some of the table columns are int/numeric/float and some of the data is empty (null) – watch out that you are not trying to import a space by accident.
For my work I work with lots of data files from third parties and this morning I was getting the following sort of error message:
Error during Transformation ‘DirectCopyXform’ for Row number 1. Errors encountered so far in this task: 1.
TransformCopy ‘DirectCopyXform’ conversion error: Conversion invalid for datatypes on column pair 134 (source column ‘q18_9′ (DBTYPE_WSTR), destination column ‘q18_9′ (DBTYPE_UI1)).
A subtle clue does present itself when previewing the data (I’m assuming you’re using the DTS import wizard) – if you are seeing empty columns instead of NULL then this is a sign there’s something there. To fix it’s just a case of opening up the data file in Excel and performing a search and replace on the ‘ ‘ (making sure that Match entire cell contents is checked of course).
Hope this helps (if anyone else out there is still using SQL Server 2000 and DTS!)
