August 7, 2009 at 3:01 am
Dear All,
I want to delete only PDF files, which are older than 3 days from a particular folder.
Is it possible to do it using SQL jobs.?. I tried with FileSystem task in business intelligence studio.
But am not able to filter only for PDF files and cannot set number of days also.
know that I can do the same using windows services or something like that...
But I am trying to do it using SQL Server 2005.
Any suggestions.?
Thanks in advance.
August 7, 2009 at 9:55 am
San (8/7/2009)
Dear All,I want to delete only PDF files, which are older than 3 days from a particular folder.
Is it possible to do it using SQL jobs.?. I tried with FileSystem task in business intelligence studio.
But am not able to filter only for PDF files and cannot set number of days also.
know that I can do the same using windows services or something like that...
But I am trying to do it using SQL Server 2005.
Any suggestions.?
Thanks in advance.
Try using maintenance task -> Maintainance task clean up task and then specify target folder and file extension in relevant boxes.
Vivek
Vivek Shukla - MCTS SQL Server 2008
August 9, 2009 at 11:31 pm
Hi,
I had tried it. Was thinking the same way. But it din't work.
Though we can mention the extension, I assume, it might be checking if the file is an SQL server BACKUP file or not.
Any other solution.?
Thanks for the reply.
August 10, 2009 at 4:02 am
I can think of couple of solutions. Please see below.
1. you can write a batch file.
2. you can write and build a simple vb app or c# app then schedule exe file to run overnight.
Please let me know if you need anymore help with this.
Vivek
Vivek Shukla - MCTS SQL Server 2008
August 11, 2009 at 8:47 am
forfiles /p "...full path..." /m "*.pdf" /d -3 /c "CMD /c del @FILE"
Tim White
August 11, 2009 at 11:05 am
San - I was able to accomplish this task in SSIS. Here are the steps I took:
1.) Add a Foreach Loop container
2.) Set properteries to For Each File Enumerator. Set folder path and *.PDF for files and retrieve fully qualified names.
3.) In variable mappings - add a new package variable type string and call it FileName. Make sure the index is 0.
4.) Add a script task and on ReadOnlyVariables line add User::FileName
5.) Click the Design Script button and add the following:
Imports System
Imports System.Data
Imports System.Math
Imports System.IO
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
' ** evaluate file for deletion **
Call evaluateFile()
End Sub
Private Sub evaluateFile()
Try
Dim filePath As String = Dts.Variables.Item("FileName").Value.ToString
Dim evalDate As DateTime = Now.AddYears(-1)
If File.Exists(filePath) = False Then
Dts.TaskResult = Dts.Results.Failure
Return
End If
Dim fileCreateDate As Date = File.GetLastWriteTime(filePath)
If CDate(fileCreateDate.ToShortDateString) < evalDate Then
File.Delete(filePath)
End If
Dts.TaskResult = Dts.Results.Success
Catch
Dts.TaskResult = Dts.Results.Failure
End Try
End Sub
End Class
5.) Change the EvalDate to your criteria (-3 days I believe).
That should be it. If all works, you can deploy the package and schedule it.
August 11, 2009 at 11:59 pm
Thank you david.
I will try the same and revert.
August 14, 2009 at 5:26 am
Dear David,
I tried what you have mentioned. It works fine but there is a small problem.It doesn't delete all files. It deletes only a single file.What am I missing. ?
Thanks.
August 14, 2009 at 9:23 am
Perhaps the is something wrong with the variable. Under the Dim filePath As String = Dts.Variables.Item("FileName").Value.ToString line add msgbox(filePath). Then run the package and see if the process is moving through all files in your directory. If the file name is staying the same, verify you have the variable index set to 0.
August 16, 2009 at 11:39 pm
.verify you have the variable index set to 0.
Thanks for your reply.
Yes. I have variable index set to 0. But still it's not moving through files I suppose. Anyways I will check by puting msgbox. Wot to do if it's not moving through files.??
Thanks.
August 17, 2009 at 4:05 am
David,
I tried by puting MsgBox(filePath). Infact it is not looping through at all. It popus up only once. Wot am I missing, I don't understand at all. In "Variable Mappings" index is 0 only. That's default and is readonly as well.
Wot to do now.?
Thanks.
August 17, 2009 at 4:06 am
david.tyler (8/14/2009)
Perhaps the is something wrong with the variable. Under the Dim filePath As String = Dts.Variables.Item("FileName").Value.ToString line add msgbox(filePath). Then run the package and see if the process is moving through all files in your directory. If the file name is staying the same, verify you have the variable index set to 0.
David,
I tried by puting MsgBox(filePath). Infact it is not looping through at all. It popus up only once. Wot am I missing, I don't understand at all. In "Variable Mappings" index is 0 only. That's default and is readonly as well.
Wot to do now.?
Thanks.
August 17, 2009 at 4:24 am
David,
Got It !. I had placed "Script Task" outside the "for Each Loop Container". Now I put it inside and it's working fine now.
Thank you somuch.
August 17, 2009 at 9:17 am
Awesome. Good to hear you have reached success.
August 27, 2009 at 9:12 pm
2 Tim 3:16 (8/11/2009)
forfiles /p "...full path..." /m "*.pdf" /d -3 /c "CMD /c del @FILE"
I do what Tim does. All that malarky with SSIS and MP. Way too much work for so little reward.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply