I know very little about DTS (yet). I need to export to a text file. Each
SQL 2000 record needs to create multiple lines in the text file. What's the
best way to do this? I'm wondering if I need to use the FileSystemObject, or
if I can do this in a more direct manner.

Thank you.

Gary

I'm not sure I understand the requirment here. Can you explain a little
further what you are trying to accomplish?

--
Simon Worth


"Gary" <GAry> wrote

I have a SQL Server 2000 table that contains data that needs to be exported
to an application as a text file. Each record in the table must be
transformed into multiple lines in the text file. From what I can read of
DTS, it expects to do a one-to-one transform, i.e., if I specify my SQL
table as input and the text file as output, each record will create only one
line in the text file.

How do I create multiple text file lines from each record? Just had another
thought. Could I specify that a field of the output record (in the text
file) gets filled in with a CR/LF, thus adding a line to the text file, yet
DTS seeing the output as only one record?

Gary

"Simon Worth" <REMOVEFIRST_simon.worth (AT) gmail (DOT) com> wrote

I've never actually tried coding a carriage return and/or line feed to the
end of a column. Sounds to me like it would in fact work to create a new
line in the text file.

Are there certain instances when you want to add new lines, or is it always
going to be multiple lines for any given record?

--
Simon Worth


"Gary" <GAry> wrote

The more I think about it, I don't think you'll be able to accomplish this
within a data pump task (and I might just be missing something here and be
totally wrong), but you could easily do it within an ActiveX script, and
write the results of the query out using a database connection and
recordset.

--
Simon Worth


"Gary" <GAry> wrote

It's a very fixed output format - it just must be written over multiple
output lines. It will be the same for each record.


"Simon Worth" <REMOVEFIRST_simon.worth (AT) gmail (DOT) com> wrote

I wondered if I might need to use a FileSystemObject in an ActiveX script
that is opened early, kept as a global variable, and written to for each
record. But it sounds like it is possible to do. Thanks for your input.

Gary

"Simon Worth" <REMOVEFIRST_simon.worth (AT) gmail (DOT) com> wrote

You can certainly take in 1 line and convert it to many lines in the
output.

Have a look here

Processing The Same Row More Than Once
(http://www.sqldts.com/default.aspx?266)