We are using DTS Text File packages to import data into SQL Server tables.
We have run into a problem with a particular file. This ~5000 lines file
fails on parsing for no apparent reason. If we try to import the file
directly using the DTS Wizard Text File Import, the preview screen hangs
after we enter the delimiter character and click Next >>. We have tried
changing the delimiter character to a standard comma, semicolon, vertical
bar, etc and all same error. If we edit the file keeping the format and
encoding (Unicode) and cut and paste 1 or 10 or 100 more lines into file, the
file parse correctly in the preview window. If we remove 1 or 10 or 100
lines it parses correctly. The file as is also imports fine into Excel. It
seems that the file as is will not import with the DTS Import Text File. We
are stumped.

Any comments suggestions welcome.

Thanks in advance.

Hello John,

I think every tool imports files differently and each is tolerant of things
that makes the others fall over. Can you isolate the rows that cause the
failures? Does this file fail parsing so it never gets to the import? I
generally try to isolate bad rows and then look at them in a HEX editor so
i can see the unseen problems a lot of the time. There is no reason if a
file is formatted correctly whay SQL Server DTS should have a fit.

Allan

Hi Allan-
Thanks for your response. I tried to isolate particular data rows and found
no identifiable pattern to what works or doesn't. I reviewed the original
file in a HEX editor and found no offending bytes. I have moved rows around
in the file: copying and pasting rows in 2 or 3 times and suddenly file
parses. Add rows (copy and paste) or removing rows leads to successful
parsing but file as is fails. Even adding one character to original file
works fine but file as is fails.

Because the file is not correctly parsed, the data will not import.

Any other recommendations?