I'm using a fixed length fields in a DTS package to import a text file.

Each line in the text file ends with a CR\LF delimiter.

The lines do not all have the same length. Blank fields after the end of the last field with data are not filled with any characters - the CR\LF delimiter simply occurs after the last field with data.

The DTS package does not seem to recognizing the CR\LF as a line delimiter when it arrives before all the fields are populated (even though it has been selected as the line delimiter for the package. When a 'short' line is encountered, enough text is stolen from the next record to finish out the 'missing' fixed length fields and the next record is ignored.

Is there a way to get the DTS package to start a new record with each occurrence of the line delimiter, or must I find some way to fill out all the null data fields at the end of each record with spaces or some other character?

Unfortunately DTS will look at the row you define at design time and then
expect to find rows of that size when you go at run time. If you come
across a short row then it will carry onto the next row to satisfy this
need.

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"SteveW" <swolf97222 (AT) yahoo (DOT) com> wrote

delimiter simply occurs after the last field with data.
selected as the line delimiter for the package. When a 'short' line is
encountered, enough text is stolen from the next record to finish out the
'missing' fixed length fields and the next record is ignored.
the null data fields at the end of each record with spaces or some other
character?

So what then is the point of a row delimiter?

In article <82B155DC-7816-430A-855E-7607AA2225C3 (AT) microsoft (DOT) com>, SteveW
<anonymous (AT) discussions (DOT) microsoft.com> writes
The point is that until you have provided all characters defined in your
fixed width columns you are still "in" a column, so what it actually
does is treat the CrLf as part of the column data. Very useful if you
are importing fixed width data that does have multiple lines per data
value. Not so useful in your case.

Your file is not a valid fixed width format. Unfortunately not everyone
agrees with what is valid, so many systems produce these truncated line
files. Best solution is to parse the file and pad them to correct
length.

I wrote a custom task for this, see the PaddingTask

Archive
(http://www.sqldts.com/Default.aspx?273)

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org