SQL Server Performance

Creating Text File from DTS Package

Discussion in 'SQL Server DTS-Related Questions' started by sueolo, Mar 23, 2005.

  1. sueolo New Member

    Hi. It is possible for one DTS package to create more than one comma delimited file? I have created a DTS package that generates a comma delimited file from my SQL query at the same time each day, but I would like the file to have a different filename for each day. Is this possible in one DTS package, or do I need a DTS package for each day of the week?

    Thank you,

    Sue
  2. satya Moderator

  3. sueolo New Member

    Hi, Satya.

    Thank you for your response

    I think I am close to making my DTS Package work with global variables. I started with something simple. However, I'm stuck on a type mismatch problem.

    My global variable, AddrDate, is a date. My Active X Script does the following:

    AddrDate = CONVERT(varchar(25), GETDATE(), 106) <== This same line works fine in my existing SQL query.

    The type mismatch is on the varchar. I've tried it several different ways and get different errors. I've never written an Active X Script before. Do you see anything wrong with this?

    What I really need is to export my data to a different comma delimited file each day of the week. I don't see a way to do this with global variables because the text file I create is part of the DTS package, not my SQL query, and the global variables examples are substituting the global var for something in the SQL query.

    Thanks very much.

    - Sue
  4. satya Moderator

    http://www.databasejournal.com/features/mssql/article.php/2225481 this helps.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. sueolo New Member

    Hi, Satya. This article is exactly what I am looking for. Thank you!

    - Sue
  6. sueolo New Member

    Hi, Satya.

    I created my DTS package and then set up a job to run the package. The pkg has an ActiveX Script to append the current day's date to my file name. However, sometimes when the job runs it just creates a file (file type = file) with no date appended and other times it creates the correct .txt file with the date appended (file type = text document). The job is scheduled to run (there is no scheduling set up on the package). Do you have any idea as to why it does not always append the date to the text file?

    Also, when I run the job manually (as opposed to when it runs because it is scheduled to run) either from the package or the job, it creates the correct .txt file with the date appended.

    Thank you.

    - Sue

Share This Page