I need to help to figure this out-->Rename files
-
Hi, All
What I want is to rename files with today's date at the end. For example, I want to change file name from "Jay.csv" to "Jay07072005.csv" or some other format that can specify date.
Therefore I dig some codes and I find this good script that I can use I think but when I try this, I dont know what I put into "LogFilePath" and "Text File (source)" ..
Any idea.. Thx in advance. And if you have other good script that you can share, that will be great.. I think every DBA needs this function as well..
Jay
BTW, I get this script from http://www.sqldts.com
****************************************************
Option Explicit
Function Main()
Dim oConn, sFilename
' Filename format - exyymmdd.log
sFilename = "ex" & Right(Year(Now()), 2)
If Month(Now()) < 10 Then sFilename = sFilename & "0" & _
Month(Now()) Else sFilename = sFilename & Month(Now())
If Day(Now()) < 10 Then sFilename = sFilename & _
"0" & Day(Now()) Else sFilename = sFilename & Day(Now())
sFilename = DTSGlobalVariables("LogFilePath").Value & _
sFilename & ".log"
Set oConn = DTSGlobalVariables.Parent.Connections("Text File (Source)")
oConn.DataSource = sFilename
Set oConn = Nothing
Main = DTSTaskExecResult_Success
End Function
****************************************************
-
The "LogFilePath" specified in the code refers to a global variable the user is using in the package. You can easily substitute the code DTSGlobalVariables("LogFilePath") to anything of your choice like "Jay" or something. You can also ignore the next line where the Connections collection is used. That's because in the code, the user is trying to set the source of the data as the file name he has come up with (That's for his own purpose).
So your code should look something like this:
****************************************************
Option Explicit
Function Main()
' You can write some code up here too
Dim sFilename
sFilename = "Jay" & Right(Year(Now()), 2)
If Month(Now()) < 10 Then sFilename = sFilename & "0" & _
Month(Now()) Else sFilename = sFilename & Month(Now())
If Day(Now()) < 10 Then sFilename = sFilename & _
"0" & Day(Now()) Else sFilename = sFilename & Day(Now())
sFilename = sFilename & ".csv"
' So you've got your filename in sFilename
' The rest is up to you
Main = DTSTaskExecResult_Success
End Function
****************************************************
Hope this helps (Apologies to Phill)
-
Here's what I use to make the month/day/year.
strYear = right(year(date()),2)
strDay = right("0" & cstr(day(date())),2)
strMonth = right("0" & cstr(Month(date())),2)
Then in one fell swoop, I concatenate all three of these strings to the strFileName:
strFileName = "Jay" & strYear & strMonth & strDay & ".csv"
this precludes checking for <10 as it adds a 0 to it and only takes the right 2 characters, so 1 = 01 and 10 = 010.
-
This is the ActiveX script we use inside a DTS package.
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim MyFSO, FName, dtMM, dtDD, dtYYYY
dtMM = month(now())
dtDD = day(now())
dtYYYY = year(now())
if len(dtMM) = 1 then dtMM = "0" & dtMM
if len(dtDD) = 1 then dtDD = "0" & dtDD
FName = "\\servername\directory\ReportName_" & dtYYYY & dtMM & dtDD & ".xls"
Set MyFSO = CreateObject( "Scripting.FileSystemObject" )
' Copy Purpose File
MyFSO.CopyFile "\\servername\directory\ReportName.xls", FName
MyFSO.DeleteFile "\\servername\directory\ReportName.xls", True
Main = DTSTaskExecResult_Success
End Function
Deborah Talley
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply