Rename File

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
"DXC" wrote:

> 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.
|||Thanks for the info but I am not a VB or ActixeX expert. Any idea on the
usage of the script ?
Thanks.
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> 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
> "DXC" wrote:
|||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:

> 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.
new
|||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..........
"beginthreadex" wrote:

> 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:
>
> --
> new
>
|||'************************************************* *********************
' 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/)