Importing NULL Values in Tab-Delimited File Using SSMS 2005

Question

I'm in the process of importing a very large tab-delimited text file using the Import Wizard in SQL Server Management Studio 2005. Some of the column values are empty, which are represented by the string value "NULL." However, when I try to import the file I get the following error message dialog:

Is there some other value I should be using instead of NULL (there are both character and numeric columns)?

Answer 1

You should consider importing into intermediate table or removing string "NULL" from input file. Another option is to manually create SSIS package.

Answer 2

If you have control of the file creation, it is better to represent null with no value. For instance ina |delimited file a records might look like:

test||1|test2||||

two pipes next to each other indicateas a null.

If you have no control over the format of the file, likely the problem is that the word null won't go directly into a table where the data type is a numeric type such as int. In this case you can make your own SSIS pacakge and use a derived column to properly popate the column or you can insert the data into a staging table where all columns are defined as varchar or nvarchar using the wizard and then use t-sql to clean and transfer the data to the production table.