I am attempting to import 6 worksheets from Excel to 6 tables in SQL Server 2000. When the package executes, I get the following error message:
"Error during Transformation 'DirectCopyXform' for Row number 801. Errors encountered so far in this task: 1. TransformCopy 'DirectCopyXform' conversion error: Destination does not allow NULL on column pair1 (source: column 'my_col_name' (DBTYPE_R8), destination column 'my_dest_col' (DBTYPEI8))."
This is strange because my excel spreadsheet has only 800 rows, and none of them are null. Is it possible that SQL Server is reading in my spreadsheet and is running right off the end of my data?
In an attempt to understand what was going on, I created a test environment with an identical destination table that allowed nulls in my_dest_col. With this, I was able to import successfully, but I got 2 null rows in my database for some (possibly all) tables.
One possible workaround is temporarily allowing nulls in the database in my production environment (while I import the data). Then I could delete the erroneously imported null values and uncheck the box to allow nulls in enterprise manager. Is it possible that this solution would cause damage to my database? It's not under constant pounding, but a few people might fill out submission forms in that interval.
Comments by mid-morning 10/24 EST would be greatly appreciated since I'll probably try the import in production at that time.