PRB: DTS May Skip More Rows than Specified for Fixed-Length Files

ID: Q247360

The information in this article applies to:

Microsoft SQL Server version 7.0

SYMPTOMS

When loading data from fixed-length fields in an ASCII file, Data Transformation Services (DTS) skips more rows than specified if the rows to be skipped are shorter than the rows you want to load.

If the rows to be skipped are longer than the data rows, then the proper number of rows will be skipped.

CAUSE

DTS expects a fixed-length file to be of fixed length, not variable length. If the first line is shorter than the maximum width of the file, DTS ignores the end-of-line markers and continues reading the next line until enough characters have been read to account for the maximum file width. All characters after this point are ignored. Therefore, DTS treats the first two lines as the line to be skipped.

If the first line is longer than the rest of the file, it will then be the longer row in the file and will be read (and skipped) properly.

WORKAROUND

Pad the rows to be skipped with spaces so that they are at least as long as the data rows to be loaded. Or use a delimiter to separate the fields instead of fixed-length fields.

MORE INFORMATION

Suppose you have the following file:


header
Smith, David   111
Brown, Julie   222
Jones, Paula   333

And you want to load it into the following table:


create table people (name varchar(15), rownum int)

If you set up a DTS import task to use fixed-length (not delimited) fields and indicate that you want to skip the first line (the one that reads "header"), since the first line is not the same length as the other three, DTS does not recognize it as a complete row of information and reads enough of the second line to meet the maximum row-length requirement. As a result, DTS skips the first two lines ("header" and "Smith, David") and will only load the last two lines. You end up with two records in your table: "Brown, Julie" and "Jones, Paula".

If you do not indicate that the first line is to be skipped, DTS tries to complete the first line with data from the second line and the following rows are not loaded correctly. If the first line is padded with spaces so that it is the same length as the remaining lines, then it will be skipped correctly and three rows will be imported.

The first row will also be skipped correctly if you are using a delimited-field file instead of fixed-length field file.

Additional query words: ignore export wizard package

Keywords : kbSQLServ700
Version : winnt:7.0
Platform : winnt
Issue type : kbprb