BULK INSERT - MISSING ROWS

Hello


Iam using:

Microsoft SQL Server  2000 - 8.00.2039 (Intel X86)   May  3 2005 23:18:38  
Copyright (c) 1988-2003 Microsoft Corporation 
Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2)

I am using BULK INSERT to import some pipe-delimited flat files into a database.

I am firstly converting the file using VB.NET, to ensure each line of the file has a carriage return (by using streamwriter.writeline), and I am also ensuring there is no blank line at the end of the file (by using streamwriter.write).

Once I have done this, my BULK INSERT command appears to work OK. This is how I am using the statement:

 BULK INSERT
  tempHISTORY
  FROM 'C:TEMPHISTORY.TXT'
    WITH
       (
       FIRSTROW = 2,
       FIELDTERMINATOR = '|',
       ROWTERMINATOR = '
'
       )

NB: The first row in the file is a header row.

This appears to work OK, however, I have found that certain files seem to miss the final line of the file! I have analysed these files incase they have an inconsistant number of columns but they don't.

I have also found that if I knock off the last column of the tempHISTORY table, the correct number of rows are imported. However, of course, I can't just discard one of the columns from the file, I need to import the entire file.

I cannot understand why BULK INSERT is choosing to miss the final line in the file, when the schema of the destination table matches the structure of the file.