Bulk Insert Fails on Last (Empty) Line

Question

OK, this seems to be a general problem but I can't find any good solutions. I'm using the Bulk Insert task in conjunction with a format file to load data from a standard tab-delmited file. Unfortunately it always fails on the last line (which is empty). I have tried removing the last line from the file and yet it still fails with a truncation/conversion error. I have total control over the file and can format it any way I wish but I simply can't find a format the the bulk insert task won't choke on. If I don't add a blank line, it fails. If I do add a blank line, it fails. I DO NOT want to count the lines in the file so I can pass a number for the -L parameter. Is there an easy way I can make this work? I'm thinking this shouldn't be so hard...

Answer 1

HI, I would suggest you to try in SSMS using command. You can then easily know where is the issue .

---

Regards, Randhir

Answer 2

Hi NTDeveloper,

I have used bulk insert task to load flat file with a blank line in to a table, and the issue was not reproduced, so that I think the issue might be caused by other reasons. Please check if the file format matches the destination table? The Bulk Insert task is the same as using BCP, you can try to upload your data using BCP command just as Randhir said and see what kind of error you will get. For more information about bulk insert, please see:
http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/  http://www.simple-talk.com/sql/reporting-services/using-sql-server-integration-services-to-bulk-load-data/ 

Please feel free to ask if you have any question.

Thanks,
Eileen