May 3, 2006 at 1:29 pm
Hi i have a job scheduled that runs every week, this job finally dumps the data in the text file. Now what i need to do is, once we get data in these text files i want to save a copy of those text files in another folder with the date extension. This should be done automatically by the job itself. I think i can do it with the active x script. Can anyone help me out.
Thanks
May 3, 2006 at 1:33 pm
FileSystemObject is what you want to look at.
Have a look at this:
May 3, 2006 at 2:06 pm
Hi thanks for your reply. Is there a way that the files get copied with the date extension to the name. I have tested the activex scripts mentioned in that url that you have mentioned but its giving me an error. This is what i did
Option Explicit
Function Main()
Dim oFSO
Dim sSourceFile
Dim sDestinationFile
Set oFSO = CreateObject("Scripting.FileSystemObject")
sSourceFile = "C:\Documents and Settings\XXX\Desktop\Test1\test1.txt"
sDestinationFile = "C:\Documents and Settings\XXX\Desktop\Test2\test2.txt"
oFSO.CopyFile sSourceFile, sDestinationFile
' Clean Up
Set oFSO = Nothing
Main = DTSTaskExecResult_Success
End Function
May 3, 2006 at 2:49 pm
What is the error?
Do those directories exist?
As far as including the date, you'd need the ActiveX equivalent of getdate() (which I believe is Now()), cast to a string and add that to the file name.
May 3, 2006 at 3:08 pm
Hi when i am tried to Execute this is what i am seeing
The number of failing rows exceeds the maximum specified
Error Code 0
Error Source: Microsoft VB Script Runtime error
Error on line 13
May 3, 2006 at 5:45 pm
This is what I could find on that:
http://blogs.msdn.com/sql_protocols/archive/2005/12/19/505372.aspx
Best guess from where I'm sitting is to make sure the source file exists and that the destination directory(FileSystemObject.FolderExists  exists.
You might also want to look at this:
http://support.microsoft.com/kb/q240221/
May 4, 2006 at 7:29 am
This is how I am naming a file with the datetime stamp and moving it to a different directory:
Function Main()
' Declare variables
Dim oFSO
Dim sSourceFile, sDestinationFile
Dim sArchiveFile, dtMonth, dtDay, dtYear, dtHr, dtMIN, dtSec
Dim TempFile, sWork, CurrentDateTime
' Set variables to current date/time
dtMonth = month(now())
dtDay = day(now())
dtYear = year(now()) ' Always 4 digits
dtHr = hour(now())
dtMin = minute(now())
dtSec = second(now())
CurrentDateTime = Now()
' Make sure variables are using 2 digits.
if len(dtMonth) = 1 then dtMonth = "0" & dtMonth
if len(dtDay) = 1 then dtDay = "0" & dtDay
if len(dtHr) = 1 then dtHr = "0" & dtHr
if len(dtMin) = 1 then dtMin= "0" & dtMin
if len(dtSec) = 1 then dtSec = "0" & dtSec
' Turn on Scripting
Set oFSO = CreateObject("Scripting.FileSystemObject")
' Set the archive file name to the current date/time
sArchiveFile = dtMonth & dtDay & dtYear & dtHr & dtMin & dtSec & ".txt"
sSourceFile = "\\Server\adt_recv$\cache_db_adt.txt"
sDestinationFile = "\\Server\adt_recv$\cache_archive\" & sArchiveFile
' Move the file
oFSO.MoveFile sSourceFile, sDestinationFile
' Clean Up
Set oFSO = Nothing
Main = DTSTaskExecResult_Success
End Function
May 4, 2006 at 7:49 am
Thanks a lot for your reply. This code works perfect but have a question. actually its deleting the source file after copying to the destination folder. I want that source file to be present there with out getting deleted. can you please let me know.
Thanks
May 4, 2006 at 7:53 am
Can you do the copy command instead of the move command?
oFSO.CopyFile
May 4, 2006 at 7:58 am
Great !!!!!!!! Thank you so much for your help. This worked.
May 4, 2006 at 8:00 am
No prob...glad it helped you.
May 4, 2006 at 8:30 am
If you need to delete the archived files after a certain time period...I am doing that too. I only need to keep them one week.
You could easily modify this to keep them for 90 days or whatever.
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
'Delete the files that are older than 1 week. JMR
Option Explicit
Function Main()
Dim oFile ' Declare Variables
Dim oFSO, InFolder, InFile, sqlFile, StartFolder
StartFolder = "\\Server\sharename\directory\"
Set oFSO = CreateObject("Scripting.FileSystemObject") 'Turn on Object Scripting
Set InFolder = oFSO.GetFolder(StartFolder)
Set InFile = Infolder.Files
'Loop Through The Directory to delete files that are more than 1 week old
For Each sqlFile in InFile
'MsgBox StartFolder & sqlFile.name
If sqlFile.DateLastModified < (Date - 7) Then
sqlFile.Delete
End If
Next 'Get next file
' End of Directory Loop
Set oFSO = Nothing ' Clean Up
Main = DTSTaskExecResult_Success
End Function
May 9, 2006 at 1:51 am
When using dates and times in the filenames, I have found it convenient to have a format like this: YYYY-MM-DD HH:MM:SS. This makes it much easier to sort the files by date and time. Here is a function that may help:
Private Function FormatMyDateTime(dt)
' format as per the ISO standard: yyyy-mm-dd hh:mm:ss
' The 'Right' function helps to ensure 2-digit values
FormatMyDateTime = Year(dt) & "-" _
& Right("0" & Month(dt), 2) & "-" _
& Right("0" & Day(dt), 2) & " " _
& Right("0" & Hour(dt), 2) & ":" _
& Right("0" & Minute(dt), 2) & ":" _
& Right("0" & Second(dt), 2)
End Function
May 9, 2006 at 6:37 am
Nice!
Thanks, I am a noob that is still learning.
I appreciate your tips.
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy