I have a text file (*.txt) created from a DTS Package. I need to change the
file name after it is created to add the current timestamp in the file name
(i.e. date and time).
Ex: The filename is 'Myfile.txt' and I need to make it 'Myfile0506051022.txt'

Is there a way I can do this ?. T-SQL syntax from DTS package ?

Thanks for any help.

You can do it before creating it.

How can I change the filename for a text file connection?
http://www.sqldts.com/default.aspx?200


AMB

Show quoteHide quote

Thanks for the info but I am not a VB or ActixeX expert. Any idea on the
usage of the script ??

Thanks.

Show quoteHide quote

you might be able to use xp_cmdshell and pass in the rename of the file just
like you would from dos.

so you already know the path the the file and the name of the file. you just
need to create a varchar with the correct formatted date/time in the name
and pass the entire rename/move command to xp_cmdshell just like from dos.

that's really about the easiest way to do this imo.


DXC wrote:

Thanks. I am already using something like this:

declare @rename varchar(255)
select @rename =
'ren "\\172.22.16.12\D$\Program Files\Microsoft SQL
Server\MSSQL\Backup\MYDB1\MYDB1*.BAK" '
+ 'MYDB1'
+ '.BAK'
exec master..xp_cmdshell @rename


to get rid of the timestamp but I don't know how to include the timestamp in
an existin file. I tried something like this but it did not work:

declare @rename varchar(255)
declare @filename datetime
Set @filename = (LEFT(GETDATE(), 12) )
select @rename =
'ren "\\172.22.16.12\D$\Program Files\Microsoft SQL
Server\MSSQL\Backup\MYDB1\MYDB1*.BAK" '
+ 'MYDB1'
+ '@filename'
+ '.BAK'
exec master..xp_cmdshell @rename


Thanks.............

Show quoteHide quote

'**********************************************************************
'  Visual Basic ActiveX Script
' Author:  Keith Kosmicki
' Date      10 May 2005
' Purpose: Change file name after its been exported to Date Time Stamp
'************************************************************************

Function Main()
    Main = DTSTaskExecResult_Success
End Function

Dim StrAccessSrc, StrNew, fso, sf, systime
StrAccessSrc    =    "\\Web1\ftp\Prime Vendor\test1"
Set fso         =     CreateObject("Scripting.FileSystemObject")
set saf        =    fso.GetFile(StrAccessSrc)
systime=now()

'Call Comment(ssf) 'ADD A TIMESTAMP AT THE END OF THE FILE SPECIFIED
Call RenameSFile(saf) 'RENAME THE SOURCE FILE WITH THE DATE FORMAT OF YYYYMMDDHHMMSS


'Sub Comment(af)
'    Dim tsa
'    Const ForAppending = 8
'    set tsa=saf.OpenAsTextStream(ForAppending)
'    tsa.writeline cstr(systime)
'    tsa.close
'End Sub


Sub RenameSFile(sf)
    StrNew=sf.ParentFolder &"\Customer-" & cstr(year(systime)) & cstr(month(systime)) & cstr(day(systime)) & cstr(hour(systime)) & cstr(minute(systime)) & cstr(second(systime))
    sf.move StrNew
End Sub


Best,
Kos User submitted from AEWNET (http://www.aewnet.com/)

'**********************************************************************
'  Visual Basic ActiveX Script
' Author:  Keith Kosmicki
' Date      10 May 2005
' Purpose: Change file name after its been exported to Date Time Stamp
'************************************************************************

Function Main()
    Main = DTSTaskExecResult_Success
End Function

Dim StrAccessSrc, StrNew, fso, sf, systime
StrAccessSrc    =    "\\Web1\ftp\Prime Vendor\test1"
Set fso         =     CreateObject("Scripting.FileSystemObject")
set saf        =    fso.GetFile(StrAccessSrc)
systime=now()

'Call Comment(ssf) 'ADD A TIMESTAMP AT THE END OF THE FILE SPECIFIED
Call RenameSFile(saf) 'RENAME THE SOURCE FILE WITH THE DATE FORMAT OF YYYYMMDDHHMMSS


'Sub Comment(af)
'    Dim tsa
'    Const ForAppending = 8
'    set tsa=saf.OpenAsTextStream(ForAppending)
'    tsa.writeline cstr(systime)
'    tsa.close
'End Sub


Sub RenameSFile(sf)
    StrNew=sf.ParentFolder &"\Customer-" & cstr(year(systime)) & cstr(month(systime)) & cstr(day(systime)) & cstr(hour(systime)) & cstr(minute(systime)) & cstr(second(systime))
    sf.move StrNew
End Sub


Best,
Kos User submitted from AEWNET (http://www.aewnet.com/)

SQL Performance
Update Statistics Fails
Help with T-SQL Coding
Problems with restoring master from a backup - SQL Server 2000
Backup corrupted after de-fragmentation