Hi, I'm Jennifer Kenney, I'm mostly SQL/TSQL, but I'm using DTS for obvious
DTS things, and I've got an issue with DTS respecting the Row Delimiters in
a text file (source) connection.

The fixed-width text file has a CRLF row delimiter, visible when I read it
in Word with the view options set to view all, and when I checked to see if
I could import it into Excel, it read the CRLF just fine BUT!!!

The individual rows are sometimes shorter than they ought to be; the last
field in the row isn't always present, and my data pump, instead of seeing
the Row Delimiter and moving the data down to the next row, is filling data
into the trailing field from the next record, then cutting off record 2
after the 30 chars that field allows and starting the next row with record
3.

Any advice?

JBK

Jennifer,

Then the file is not fixed-width in a strict sense. If you opened the file
and filled up missing width it would be working.

Ilya

"Jennifer Kenney" <jkenney (AT) pembrooke (DOT) com> wrote

Any ideas about how to fill that in automatically?

"Ilya Margolin" <ilya_no_spam_ (AT) unapen (DOT) com> wrote

I handle things like this with ActiveX scripts. My preference for text
manipulations is PERL. If you have ever programmed in C or even C++,
then PERL isn't to terribly difficult and is well suited for text
manipulation.

I think you'd want to read the file line by line, each time into a
local string variable, and check the length of the string, then output
that string with additional spaces to ensure it is the proper length.

<shumaker (AT) cs (DOT) fsu.edu> wrote

there?

Jennifer,

That is the idea. Plus to what shumaker (AT) cs (DOT) fsu.edu suggested you can do it
in an ActiveX task in the same DTS package prior to reading the file by data
pump. Look up FileSystemObject documentation.

Ilya

"Jennifer Kenney" <jkenney (AT) pembrooke (DOT) com> wrote

Thanks, everybody who wrote in on this. This morning I put the text file
through an intermediary data pump to a holding text file, setting the
properties on the original file to character delimiter (using a character
that does not appear in the file), and setting the transformation to

DTSDestination("Col001") = DTSSource("Col001") + Space(
1321-Len(DTSSource("Col001")) )

where '1321' is the desired record length. Setting the field delimiter to a
non-existent character forced it into recognizing the row delimiter, the
data pump does its thing almost instantaneously, and DTS reads the resulting
file flawlessly.

Many thanks!

JBK

"Jennifer Kenney" <jkenney (AT) pembrooke (DOT) com> wrote

Hi Jennifer,

I too have the same issue. Can you please explain me in detail wt to be done.

Thanks, everybody who wrote in on this. This morning I put the text file
through an intermediary data pump to a holding text file, setting the
properties on the original file to character delimiter (using a character
that does not appear in the file), and setting the transformation to

DTSDestination("Col001") = DTSSource("Col001") + Space(
1321-Len(DTSSource("Col001")) )

where '1321' is the desired record length. Setting the field delimiter to a
non-existent character forced it into recognizing the row delimiter, the
data pump does its thing almost instantaneously, and DTS reads the resulting
file flawlessly.

Many thanks!

JBK


From http://www.developmentnow.com/g/103_...iter-probs.htm

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com/g/