Unexpected EOF in bcp data file

[TrailerTrash]

I am importing a flat file and would like to automate the procedure using bcp.
When I bring the file in using Data Transformation Services I get 18,960 records. No errors, all the records.

Using bcp I get the above error after it successfully copies 108,917 records. There is no problem with my data file - I examined it w/ Word and found no goofy characters surrounding the last good record. It is rebuilt everyday from a mainframe. Yesterday it stopped at a slightly different spot.

Changing the batch size doesn't help - It always stops at the same place. Changing Max Errors doesn't help either.

I copied the flat file to my local drive and tried again. It still errors out at the same record.

MSoft Technet says you can get the error w/ an input file of 4GB, mine is only 21MB.

The Transaction Log file isn't full either. It's got plenty of space.

I've run out of ideas.

 

[tlbroadbent]

Is the import file delimited or fixed length? If fixed length, are you using a BCP format file? Which version of SQL Server are you running? Does the format file match the version? Does the format file match the flat file layout? Does the record that BCP errors on match the format file?

If the file is delimited, you don't need a format file. In this case, does the record in error contain the correct number of delimiters?

May I ask why you would change from DTS to BCP if DTS works? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[TrailerTrash]

Good questions -
The import file is fixed length; I am using a format file which bcp created for me, which I then tuned because there is an empty field full of white space at the end I don't want.
Running SQL 7.0.

The record that bcp errors on, the subsequent record and the prior record have abolutely no distinguishing characteristics. It was a different record yesterday because the input file is recreated on a daily basis.

The records that are imported, all 108,917 of them, look fine. I'm just missing the final 47.

dts is labor intensive, I want to automate the process so it runs without me. bcp I can schedule. Programming dts appears beyond my reach. I can't even spell "C".

 

[tlbroadbent]

I'll think about possible reasons for the BCP failure. I do not have a ready answer. Have you searched Microsoft's Knowledgebase?

DTS programming is simple. The DTS Wizard will handle just about everything. If you need to program transformations, it can be done in VB script or Javascript.

DTS packages can be saved and scheduled to be executed by SQL Agent Jobs. I like dealing with the GUI interface much better than the command line interface to BCP. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[TrailerTrash]

Hey - I never saw that button "Save Package" before. That has the potential to save my bacon.

Thanks.

 

[eja2000]

DTS is easy when you know the name of the file..
what if you dont?
lets say all the txt files begin with 'batch_import' but the last part is a datestamp..
how do you use DTS to achieve that?

 

[ClayG]

BCP is very sensitive about its separators and fields lengths. For example, if your unwanted blank field is missing from one row then bcp might still skip n characters into the middle of the next row before filling the next rows first field with the wrong data - eventually it gets to EOF with an incomplete row. Thats just one possibility. Or there could be no final row terminator on the last row ..

You can execute BCP with First and Last options to check individual rows and find the bad one. Be careful how you look at a bcp data file - for example, Char(0) may be used by BCP, and most text editors dont deal with it well (word-processors or spreadsheets - forget it). If nothing better, try 'DOS'
EDIT /80 file.dat