Results 1 to 8 of 8
  1. #1
    Join Date
    May 2002
    Posts
    55

    Unanswered: 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

    Guyang

  2. #2
    Join Date
    Jan 2004
    Location
    Ottawa, Canada
    Posts
    53

    Re: DTS output file with "Append " mode?

    Did 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

  3. #3
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    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.

  4. #4
    Join Date
    Jan 2004
    Location
    Ottawa, Canada
    Posts
    53

    Exclamation excel limitations

    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.

  5. #5
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    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.
    -bpd

  6. #6
    Join Date
    Jan 2004
    Location
    Ottawa, Canada
    Posts
    53

    Post to bpdWork

    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.

  7. #7
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    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.
    -bpd

  8. #8
    Join Date
    Jan 2004
    Location
    Ottawa, Canada
    Posts
    53

    bpdWork

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •