DTS - SQL Server - ASP Free

ASP Free Forums Sponsor:

December 28th, 2004, 01:21 PM

samcneal

Contributing User

Join Date: Dec 2004

Posts: 75

Time spent in forums: 13 h 26 m 2 sec
Reputation Power: 4

DTS - SQL Server

I am having a problem with my DTS package. I want to move old backup files that are 10 days old. However, it is not passing the file name. Prior to adding the date as a condition, it moved all the bak files within the folder to the other folder. Any suggestions/

' Move File
Option Explicit
Function Main()
Dim oFSO
Dim sSourceFile
Dim sDestinationFile
Dim oFile
Set oFSO = CreateObject("Scripting.FileSystemObject")
sSourceFile = "C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Data\*.bak"
sDestinationFile = "C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Oldbakfiles\"

Set oFile = oFSO.GetFile(sSourceFile)
If oFile.DateCreated < Date Then
oFSO.MoveFile sSourceFile, sDestinationFile
Else
Main = DTSTaskExecResult_Failure
End If

' Clean Up
Set oFSO = Nothing
Set oFile = Nothing

Main = DTSTaskExecResult_Success
End Function

December 28th, 2004, 03:31 PM

lewy

Alter Ego Wizard

Join Date: Jun 2004

Location: Edinburg Tx

Posts: 4,381

Folding Points: 1009 Folding Title: Novice Folder

Time spent in forums: 1 Month 1 Week 1 Day 23 h 7 m 43 sec
Reputation Power: 1488

I've never tried to Move files this way.
You may want to try and first grab all the files in the directory and then test for the date modified on a one by one basis then if the file meets the criteria, move it.
This is how I would do it:

Code:

 
	 Dim testdate
	 testdate = Date() 
	 path = Server.MapPath("/your back up path file/") 'rememer to replace this with the folder path to your backups
	 set fs = CreateObject("Scripting.FileSystemObject")
	 set folder = fs.GetFolder(path)
 
	 for each item in folder.Files
		 If item.DateLastModified < testdate
			response.write "<br>" & item.name & " Date Modified = " & item.DateLastModified
			'Move the file to its new directory
		 End if
	 next

This will give you the files in your directory that were last modified before today.
Moving the file should be straight forward from this point on.

December 28th, 2004, 03:42 PM

samcneal

Contributing User

Join Date: Dec 2004

Posts: 75

Time spent in forums: 13 h 26 m 2 sec
Reputation Power: 4

DTS Package

Thanks for the feedback. I am getting an error in the server path. This is what I have:

Function Main()
Dim testdate
testdate = Date()
'sSourceFile = "C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Data\*.bak"
sDestinationFile = "C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Oldbakfiles\"
path = Server.MapPath("\\local\C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Data\*.bak") 'rememer to replace this with the folder path to your backups
set fs = CreateObject("Scripting.FileSystemObject")
set folder = fs.GetFolder(path)

for each item in folder.Files
If item.DateLastModified < testdate Then
response.write "<br>" & item.name & " Date Modified = " & item.DateLastModified
fs.MoveFile folder, sDestinationFile 'Move the file to its new directory
End if
next
Main = DTSTaskExecResult_Success
End Function

December 28th, 2004, 03:50 PM

samcneal

Contributing User

Join Date: Dec 2004

Posts: 75

Time spent in forums: 13 h 26 m 2 sec
Reputation Power: 4

I actually modified the path to reflect what I should have. Now I am getting a response error. What exactly does that line suppose to do?

Quote:

Originally Posted by samcneal

December 28th, 2004, 04:03 PM

lewy

Alter Ego Wizard

Join Date: Jun 2004

Location: Edinburg Tx

Posts: 4,381

Time spent in forums: 1 Month 1 Week 1 Day 23 h 7 m 43 sec
Reputation Power: 1488

Server.MapPath only works, if you have your files residing in the wwwroot folder, when I tested this I used the wwwroot folder. Change this to something like "c:\test\your folderpath\"
This line response.write "<br>" & item.name & " Date Modified = " & item.DateLastModified simply writes out to the browser file names that match your criteria.

Test the code without it

And try it without a function call
Instead use:

Code:

 
Dim testdate
testdate = Date() 
 
sDestinationFile = "C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Oldbakfiles\"
path = "C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Data\") 
 
set fs = CreateObject("Scripting.FileSystemObject")
set folder = fs.GetFolder(path)
 
for each item in folder.Files
  If item.DateLastModified < testdate Then
	fs.MoveFile folder, sDestinationFile 'Move the file to its new directory
  End if
next

December 28th, 2004, 04:46 PM

samcneal

Contributing User

Join Date: Dec 2004

Posts: 75

Time spent in forums: 13 h 26 m 2 sec
Reputation Power: 4

DTS script problem

This is what I was trying, with both codes, I'm either getting file does not exist or file already exists.

' Move File
Option Explicit
Function Main()
Dim Fso
Dim Folder
Dim F
Dim Destinationfile
Dim Date

Date = Now()
Destinationfile = "C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Oldbakfiles"
Set Fso = Createobject("Scripting.Filesystemobject")
Set Folder = Fso.Getfolder("C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\DATA")
For Each F In Folder.Files
If F.Datecreated < Date Then
Fso.Movefile F, Destinationfile
End If
Next
Set Fso = Nothing
Set Folder = Nothing
Set F = Nothing

Main = Dtstaskexecresult_Success

End Function

December 28th, 2004, 04:59 PM

lewy

Alter Ego Wizard

Join Date: Jun 2004

Location: Edinburg Tx

Posts: 4,381

Time spent in forums: 1 Month 1 Week 1 Day 23 h 7 m 43 sec
Reputation Power: 1488

Use this code, it should work.

Code:

<%@language=Vbscript %>
  
<% 
   
	  '*************************************************  ********************************
	  '******************** This portion of the asp script moves files in ****************************
	  '******************** older than today's date to a new directory   ***************************
	  '*************************************************  ********************************
	  Dim test
	  test = date()
	  path = "C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\DATA"
	  sDestinationFile = "C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Oldbakfiles"
	  
	  set fs = CreateObject("Scripting.FileSystemObject")
	  set folder = fs.GetFolder(path)
	  
	  for each item in folder.Files
		 if item.DateLastModified < test Then			
			fs.MoveFile folder&"\"&item.Name, sDestinationFile&"\" 
		 End If
	 next
   
	
%>

December 28th, 2004, 05:08 PM

samcneal

Contributing User

Join Date: Dec 2004

Posts: 75

Time spent in forums: 13 h 26 m 2 sec
Reputation Power: 4

Thanks that work. Would it be difficult to have it check if it is 10 days old than the date()? Should I add n days to the If stmt?

December 28th, 2004, 05:18 PM

lewy

Alter Ego Wizard

Join Date: Jun 2004

Location: Edinburg Tx

Posts: 4,381

Time spent in forums: 1 Month 1 Week 1 Day 23 h 7 m 43 sec
Reputation Power: 1488

Just change the if then statement to
if (item.DateLastModified) > test-10 AND item.DateLastModified < test Then ....

This statement will give you what you are looking for.

#10

December 28th, 2004, 05:39 PM

samcneal

Contributing User

Join Date: Dec 2004

Posts: 75

Time spent in forums: 13 h 26 m 2 sec
Reputation Power: 4

Thanks. I truly appreciate your help. I have files dated back to 12/16 and the most current file was created 12/21. It moved the most current and left the older files. Andy ideas?

#11

December 28th, 2004, 05:42 PM

samcneal

Contributing User

Join Date: Dec 2004

Posts: 75

Time spent in forums: 13 h 26 m 2 sec
Reputation Power: 4

I think it should be datecreated instead of datelastmodified. I tried this to see if it made a difference and there was no change.

#12

December 28th, 2004, 07:26 PM

lewy

Alter Ego Wizard

Join Date: Jun 2004

Location: Edinburg Tx

Posts: 4,381

Time spent in forums: 1 Month 1 Week 1 Day 23 h 7 m 43 sec
Reputation Power: 1488

My bad, the if then statement should be
if (item.DateLastModified) < test-10 then ....
This should move files that are older than 12/18/2004

#13

December 29th, 2004, 10:33 AM

samcneal

Contributing User

Join Date: Dec 2004

Posts: 75

Time spent in forums: 13 h 26 m 2 sec
Reputation Power: 4

Thanks!!!! That was exactly what I needed. Can you suggest a good ActiveX book for beginners?

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: