SQL Server: Bulk Insert Task Errors

[eeliao]

Hello Experts,

I am dire need of help, hopefully you can provide some insight. I am involved with importing large amounts of data from text files to SQL Server. Formerly I have been performing this function manually via DTS > Import Data... and using the wizard. I understand that this may be the least efficient way of importing data and that Bulk Insert may be a more friendly option. The DTS Designer is easy to use and I am able to select my source and my destination, however I cannot get the task to execute. There has been many reasons for errors. Below are a few of my error messages:

"Unexpected End-of-File (EOF)"

"Column is too long...make sure field terminator and row terminator are specified correctly"

I realize that it may relate to the data I have, however when I import using the DTS wizard, I don't receive the error messages.

If someone may provide some light on the world of Bulk Insert it would be greatly appreciated. Thanks.

[skhanal]

What type of field terminator are there in the data files?

Try to run it by specifying the field and row terminators explicitly in BULK INSERT statement.

[eeliao]

thanks so much for the reply. explicitly stating the end of the file does help.

a similar problem i am having is that a set of large data files i have to import has the last column filled w/ blank spaces. therefore my scheduled bulk inserts error out due to the fact the column length is too long. i don't need the last row so how do i explicitly give a row terminator or omit the last column. the file i am dealing w/ are 100 -300 MB so it would be difficult and time consuming to open up and delete the spaces.

any light you can shine on this subject would be so helpful. thanks.