I am importing a text file into a table within DTS. For some reason, the
import works fine but stops at line 1265 of the text file. Is there some
limitation as to how many characters can be imported in one transaction?

Thanks.

Please clarify, You only get 1265 rows successfully imported, OR at row
1265, it aborts and nothing is imported?

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous


"bill" <wbeatty (AT) prodigy (DOT) net> wrote

I get 1265 rows imported and then it stops, but the DTS package completes
successfully. I tried moving the rows around in the text file and then
1264 rows imported, so it almost seems that there is some sort of length
limitation. I checked to make sure there weren't any strange characters in
the text field that would cause it to stop.

Thanks.


"Arnie Rowland" <arnie (AT) 1568 (DOT) com> wrote

I have successfully imported hundreds of thousands of rows and never
experienced a 'length limitation'.

There has to be something particular about your DTS package and/or data that
is terminating at that position in the import file.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous


"bill" <wbeatty (AT) prodigy (DOT) net> wrote

I imported the text file into a new table without the DTS package, just
right clicking on Tables and Importing Data and it did the same thing.
Stopped at 1265 rows.

I have another text file that is much larger that imported with no problem
whatsoever. This has me baffled because I can't find any odd characters in
the file.


"Arnie Rowland" <arnie (AT) 1568 (DOT) com> wrote

Perhaps there is a 'hidden' EOF (End of File marker) in the file.

Can you open it in a ascii text editor, something like TextPad? And then
examine for a EOF.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous


"bill" <wbeatty (AT) prodigy (DOT) net> wrote

I'm doing that now. I downloaded a Hex editor to see if I see something
like that. I'm sure there has to be a logical explanation and once I find
it it will probably stick out like a sore thumb. Thanks.


"Arnie Rowland" <arnie (AT) 1568 (DOT) com> wrote

Here's the problem. Maybe you may know a way around it. First of all, it's
not stopping at 1265 records, it's only returning 1265 records. I didn't
know that earlier. It's actually skipping SOME of the records. When doing
the text import, I have to use fixed field delimited. The last field varies
between 7 and 8 characters long. SQL Server puts the last column delimiter
line after the 8th character and every field that is only 7 characters long
has a carriage return after it. Because of this, every time a record has
this field with only 7 characters, it skips the next line.

I need to find out if they can format the text file differently with commas
or something.


"bill" <wbeatty (AT) prodigy (DOT) net> wrote

I'm glad you got to the bottom of the problem. It seems clear that the
formatting issue is the cause, as you concluded.

Obviously, the file is a combination of fixed and variable width fields.
Unless there is a field delimiter, you have no choice except to request that
the file be formatted consistently -either fixed or variable.

If you do have a field delimiter, then change from fixed field import to
variable field import.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous


"bill" <wbeatty (AT) prodigy (DOT) net> wrote

Thanks for your help. One solution is to have the person that generates the
file change the columns around so that the last field is ALWAYS a fixed
width.



"Arnie Rowland" <arnie (AT) 1568 (DOT) com> wrote