DTS Package - Append Results to Text File

I have created a DTS package which transfers data from a SQL 2000
server table to a text file. Each time the package is executed the
data in the text file is replace with the new data from the current
execution of the package. Can DTS append new text to a text file and
not replace it each time the package executes. Is there an option or
seeting for this? I havent seen one. Can anyone help me out on this.
Thank you,

Brett"bdwgarth" <bwalke@.lbrspec.com> wrote in message
news:1105716432.160263.235590@.c13g2000cwb.googlegr oups.com...
>I have created a DTS package which transfers data from a SQL 2000
> server table to a text file. Each time the package is executed the
> data in the text file is replace with the new data from the current
> execution of the package. Can DTS append new text to a text file and
> not replace it each time the package executes. Is there an option or
> seeting for this? I havent seen one. Can anyone help me out on this.
> Thank you,
> Brett

http://mssql.meetholland.com/message/162162.aspx

Simon|||I doubt it will let you handle merging file text thru DTS. Anyone
correct me if I am wrong..!

DTS output file with "Append " mode?

I have set up an export which use DTS service to extract data from a table to a Flat ( text) format file.

Is there any way setup this simple DTS as a " APPEND" mode? so that next time's data will append to the flat file output?

because the default way I can do for this flat file is " overwrite " .
the second time's run will always overwrite the privious run result.
even save the DTS scipt to VB file still can't find where to setup output file write mode.

Appreciate any help

Regards

GuyangDid you ever get an answer on this? I have the same requirement

Originally posted by guyang2000
I have set up an export which use DTS service to extract data from a table to a Flat ( text) format file.

Is there any way setup this simple DTS as a " APPEND" mode? so that next time's data will append to the flat file output?

because the default way I can do for this flat file is " overwrite " .
the second time's run will always overwrite the privious run result.
even save the DTS scipt to VB file still can't find where to setup output file write mode.

Appreciate any help

Regards

Guyang|||If you transform the data to a excel sheet instead of a flat file its always appended. you might think of making the DTS package in such a way that first step will move data to a excel file(which is always appended) and the second step will transform data from that excel to a text file. this might solve your purpose.|||Thank-you but,

That won't work for me. The number of entries exceeds the limitations of Excel.

Currently my only viable option is to export subsets into a number of text files and concatenating them into one text file.

To me this is less than ideal.|||I was about to post the same question, but hoping there was an append mode for bcp. There is no switch listed for appending, but I really need it.

The alternative for me would be to post results to a temporary table, then output that table to a file, but I am hoping to avoid it.|||I am currently doing exactly that. But be careful if the order is important..

I am transferring a varchar value into an ntext value in another table (only way to maintain paragraph formatting) to generate LDIF files for an X500 directory. I have attempted to force a sort order through a series of SQL UPDATE statements.

Unfortunately, the sort order gets destroyed by the size of the export to the point that the text file does not match the order of the table. Indexing of this table is impossible.

Originally posted by bpdWork
I was about to post the same question, but hoping there was an append mode for bcp. There is no switch listed for appending, but I really need it.

The alternative for me would be to post results to a temporary table, then output that table to a file, but I am hoping to avoid it.|||My requirement is to build an SAP feed of billing data for a customer. Since ordering is very important (the file is actually broken up into blocks by cost center), I was thinking of a temp table cosisting of either an int or timestamp id, and a varchar(4000), which is much larger than any line can be. I simply output ordering by the id field.

I thought about building a text field and appending, but the added work of pointers, plus adding the end of row terminiators, etc. is a pain in the ass, and seems like a breeding ground for bugs. Sounds like our requirements are different though.|||Take a serious look at an ntext (blob) as opposed to a nvarchar(4000). You can maintain the format and end charachters in an ntext to text output.

Originally posted by bpdWork
My requirement is to build an SAP feed of billing data for a customer. Since ordering is very important (the file is actually broken up into blocks by cost center), I was thinking of a temp table cosisting of either an int or timestamp id, and a varchar(4000), which is much larger than any line can be. I simply output ordering by the id field.

I thought about building a text field and appending, but the added work of pointers, plus adding the end of row terminiators, etc. is a pain in the ass, and seems like a breeding ground for bugs. Sounds like our requirements are different though.

DTS import

ive successfully imported the data i need into a table, but now i need to
append to that data another field from the original source into data thats
already there
eg original source was poor - had a diary function that could be either
ID,Account Manager,Diary or ID,Senior Account Manager,Diary
in sql i have ID,EnteredBy,Diary
i imported the data from Access no problem but i could only import the
account manager field - now i need to append in senior account manager to
the data thats already there
heh not sure if im making myself clear
markCould you supply a DDL of the source table and the SQL Server table and a
sample of the data and what you want it to look like?
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"mark" <mark@.remove.com> wrote in message
news:dwHqc.24$pz2.23@.newsfe3-win.server.ntli.net...
> ive successfully imported the data i need into a table, but now i need to
> append to that data another field from the original source into data thats
> already there
> eg original source was poor - had a diary function that could be either
> ID,Account Manager,Diary or ID,Senior Account Manager,Diary
> in sql i have ID,EnteredBy,Diary
> i imported the data from Access no problem but i could only import the
> account manager field - now i need to append in senior account manager to
> the data thats already there
> heh not sure if im making myself clear
> mark
>|||"mark" <mark@.remove.com> wrote in message news:<dwHqc.24$pz2.23@.newsfe3-win.server.ntli.net>...
> ive successfully imported the data i need into a table, but now i need to
> append to that data another field from the original source into data thats
> already there
> eg original source was poor - had a diary function that could be either
> ID,Account Manager,Diary or ID,Senior Account Manager,Diary
> in sql i have ID,EnteredBy,Diary
> i imported the data from Access no problem but i could only import the
> account manager field - now i need to append in senior account manager to
> the data thats already there
> heh not sure if im making myself clear
> mark
Well, I don't understand the detail of what you're trying to do.
There are three options spring to mind though.
All start with you adding your new field to the table.
1) With DTS you can do all sorts of things in script.
There's probably a way of updating the data directly via a DTS
package, but I reckon if you have to ask then this ain't the route you
want mate.
2) Whack the data into a new table. Put a bit of T-SQL together to
update
with this and then drop the new table.
3) Connect access to the SQL database using a dsn and attached tables
and write the update in access. This may be slow depending on your
connection and amount of data but I suggest it because maybe you're
more used to working in Access.
HTH