Dear all,

I am using SQL 2000 SP4.

I want to use DTS to transfer a text file to a SQL table.

The text file contain one string and I transfer it to a column.

However, I found that sometimes, some records are missed.

The length of the records are the same.

Does anyone encounter similar problem?

Best Regards,
Ivan

Further information here is needed in order to understand better your request.
It's hard to provide info without seeing more details...

Anyway, have you enabled specific log onto the pump task?
Transform Data Properties->Options->Exception File

Hello Ivan,

are the rows terminated correctly (Use a HEX Editor).? Can you narrow the
file down to get to the first row that starts to get missed?


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

I have checked the data using HEX Editor and found it had no problem.

I have check the data again and found the problem.

The text files contain the strings with 21-22characters

When DTS read the string with 21 characters, it cannot read the next string
with 22 characters.

So, the missing strings are only the one with the previous strings had 21
characters.

Is there anything I set wrong?

Ivan

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com>
???????:f0343b03221c48c830f0886628e0 (AT) msnews (DOT) microsoft.com...

Hello Ivan,

So are you expecting 21 or 22 characters in a row?

Is this a single column string?

For this I would usually specify a delimited string with CRLF as the Row
Terminator. It works for me.




Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Both 21 & 22 characters are accepted.

It is a single column string.

Besides, I am already using delimited string with CRLF as the Row
Terminator.

Ivan

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com>
???????:f0343b03225e18c831b87756a950 (AT) msnews (DOT) microsoft.com...

Hello Ivan,

So if you don't care if the string is 21 or 22 characters in length but in
any event it will be terminated with a {CR}{LF} then would defining the file
as comma delimied not work for you? You should see the string come through
as a single column.



Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

I did see the string come through as a single column.

For example, I have 15 records to insert, and 5th & 12th record had 21
characters, and the remain have 22 characters.

after DTS input, I found the 6th & 13th record did not appear in the result
table.

only totally 13 records are inserted.

Ivan

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com>
???????:f0343b0322bb88c8328073736290 (AT) msnews (DOT) microsoft.com...

Hello Ivan,

Are you sure you have your defined row terminators on the end of each row?

Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Yes. I have checked the Hex Mode.

All strings end with Hex 0D 0A, including the 21-character string.

Ivan

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com>
???????:f0343b0322e3f8c8330ede442130 (AT) msnews (DOT) microsoft.com...