Hi, there,

I have one DTS runs every day to generate one text file from one table in a
database of SQL Server 2000. The output file path I use is
\\web2\export\report.txt. I schedule the DTS to run at 10:00pm every day,
and each time it runs it will generate a text file "report.txt" on the
folder "\web2\export".

My problem is, I have to rename the output file to a different one like
"20050623_report.txt" every day, otherwise the output file will be
overwritten by the next run of the DTS.

My question is, is there a way to generate a text file with the date of the
DTS run attached to it? (For example: today the DTS will generate a file
name "20050623_report.txt", while tomorrow it will generate a new file name
"20050624_report.txt" automatically). Because I don't want the old files to
be overwritten.

Thanks so much in advance,

Jamie

You can use an "ActiveX script task" to fill a Global variable with the file
name and then "Dynamic Properties Task" to assign this value to the
destination connection.

Francesco Anti

"Jamie" <gsadd (AT) yahoo (DOT) com> wrote

To add to Francesco's comments, you might find the following link useful if
you are new to ActiveX scripts:
http://www.w3schools.com/vbscript/default.asp

Alternatively, here is a bit of VB you can edit that I have used in an
ActiveX script to name output files:
'************************************************* ***********
' Visual Basic ActiveX Script
'************************************************* ***********

Function Main()
Dim objConn, strFilename


' Filename format - \\Server\Drive\Folder\Filename_YYYYMMDD.txt
'Start of destination file name
strFilename = "\\Server\Drive\Folder\Filename_"
'Add 4 digit year + 2 digit month + 2 digit day to file name
strFilename = strFilename & Year(Now()) & Right("0" & Month(Now()),2) &
Right("0" & Day(Now()),2)
'Add file extension to file name
strFilename = strFilename & '.txt'

'Set the destination for the connection to the string just built above
Set objConn = DTSGlobalVariables.Parent.Connections("TextFileCon nectionName")
objConn.DataSource = strFilename

'Tidy up - clear connection object
Set objConn = Nothing

Main = DTSTaskExecResult_Success

End Function


Hope this helps

Paula

"Jamie" wrote:

Thank you so much for the detailed help! It's really helpful.

Jamie
"PaulaPompey" <PaulaPompey (AT) discussions (DOT) microsoft.com> wrote