I’m hoping someone can help me with a problem I’m having importing some data from a tab-delimited text file into a SQL Server 2005 database.
These files imported fine using the Import Wizard in SQL Server 2000, but in this version the values in numerical columns that should be NULL (i.e. they are blank in the flat file) are coming in as zeroes instead. This is bad because it means that when I average across the column I get the wrong result.
How can I get the blank values to import correctly as NULL instead of zero?
I hate to sound pedantic about this, but what is the data type of the columns in hte table you’re importing to? Have you check that NULLs are allowed, and that you don’t have a default value set for those columns in the table?
Enright3: I have specified the data types of the columns (e.g. single-byte signed integer) and the field definitions in the destination table (e.g. tinyint) in the SSIS Import/Export Wizard in order that they correspond, and ensured that the destination columns are set to ‘null’ rather than ‘not null’.
I take it you’re importing to a new table that the I/E Wizard creates. What the Wizard is doing is creating an SSIS package behind the scenes, and then executing that. I suspect that the package is modifying the NULLs, leading to the change in behaviour. You could either save and modify the package yourself, if you’re comfortable editing SSIS packages, or a quick and dirty workaround would be to use the SQL Server 2000 dtswiz.exe, which should retain the NULL values.
I suppose it depends on how long you plan on using this particular process, but I would lean toward havingi t create the package, and then editing the package using BIDS (Business Intelligence Development Studio). You can see exactly what it’s doing to the file in the SSIS package and adjust the proprties in there, and even add some converting steps should they need to be added. It’s a little threatening to a beginner, but it is something that can be handled with a little patience and a book from the library.