FIX: DTS Transfer Reports Error "Non-whitespace characters in row" Even Though All Rows are Imported From Text File (300042)

SYMPTOMS

A Data Transformation Services (DTS) package that imports a text file may report an error message, non-whitepsace characters in a row, even though the row is imported successfully. The error message that occurs is:

Error at Source for Row number xxxx. Errors encountered so far in this task: 1. Too many columns found in the current row; non-whitespace characters were found after the last defined column's data.

The error source is the Microsoft Data Transformation Services Flat File Rowset Provider.

Even though an error occurs, all rows in the input file are successfully imported.

RESOLUTION

To resolve this problem, obtain the latest service pack for SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

WORKAROUND

To work around this problem and import the file either:

Modify the DTS package to use an Execute Process Task and then use bcp.

-or-
Use Execute SQL Task and the Bulk Insert Transact-SQL command.

MORE INFORMATION

If the input file is improperly formatted the error may occur, which is the expected behavior. Therefore, you need to open the input file in an editor and determine if the row is improperly formatted. You should also view the file by using a hexadecimal editor.

If you open the file by using an editor and the row appears to be formatted correctly, cut and paste that row into its own file. Then, if that file is imported without any errors there may be nothing wrong with the format of the row. The error may also occur if the rows above or below the row identified are improperly formatted. Therefore, cut and paste a few rows above the row reporting an error and the remaining rows in the file into a separate file. If that file is imported without any errors then there may be nothing wrong with the format of the row.