Importing NULL Values in Tab-Delimited File Using SSMS 2005

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 开发者_JAVA百科be using instead of NULL (there are both character and numeric columns)?

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

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.