Delete Old files in the destination folder

  • Hi,

    I want to export data from sqlserver to text file on daily basis.While doing this if the files are older than 5 days in the destination folder then thst files should be automatically delete.How can i do this?

    Pleaseeeeeeee helpppppppppp meeeeeeeeeee.

    Cheers

    Abhas.

  • See my answer here:

    http://www.sqlservercentral.com/Forums/Topic1030738-364-1.aspx

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Abhas,

    You can use the Execute Process Task and File System Task to perform your operations. Create the necessary variables so that you can configure your package and applying these variable in expressions to configure your tasks to execute as desired.

    It's likely that this will all or at least in part, be contained in a Foreach loop container so that you can cycle through files.

    Paul

  • Paul_Blackwell (12/6/2010)


    Abhas,

    You can use the Execute Process Task and File System Task to perform your operations. Create the necessary variables so that you can configure your package and applying these variable in expressions to configure your tasks to execute as desired.

    It's likely that this will all or at least in part, be contained in a Foreach loop container so that you can cycle through files.

    Paul

    How would you use the Execute Process Task? Can you give an example?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Hi Paul,

    Can you give example and explore it?

  • Abhas,

    Go ahead and look at the attachment. I don't know what I'm doing wrong on the Execute Process task (UnZip File), but it's not unzipping (expanding) the zipped log file properly.

    Expand the project to a location and update the package variables to point to the new paths. Look at the expressions on the conntections as well as in the Foreach loop.

    Sorry I didn't take more time to annotate; I've had a long day.

    Good luck and if you need me to explain anything, send me a message.

    Paul

  • Hi abhas

    To delete the files from Destination Folder

    U need to follow the following steps

    Create one variable varFilePath

    Use a for each loop container,which iterate through each file in Destination Folder and assign the path of the file to variable varFilePath

    Use one script task inside ForeachLoop Container,the task will first get the created date of the file and then calucalte the difference between

    current date and created date ,if the difference is more than 5 days then it will delete the file from the Destination Folder

    Write the following code in ScriptTask(the code is in vb)

    Again U need to pass variable varFilePath in script task as read only.

    Public Sub Main()

    Dim path As String

    path = Dts.Variables("User::varFilePath").Value.ToString()

    If File.Exists(path) = False Then

    Dts.TaskResult = Dts.Results.Failure

    Return

    End If

    Dim fileCreateDate As Date = File.GetCreationTime(path)

    Dim tsTimeSpan As TimeSpan

    Dim noOfDays As Integer

    tsTimeSpan = Now.Subtract(fileCreateDate)

    noOfDays = tsTimeSpan.Days

    If noOfDays >= 5 Then

    My.Computer.FileSystem.DeleteFile(path)

    Dts.TaskResult = Dts.Results.Failure

    Else

    Dts.TaskResult = Dts.Results.Success

    End If

    Dts.TaskResult = Dts.Results.Success

    End Sub

    Hope this will solve your problem

    In case any query let me know

  • Thanks rasmi.

    Now i get the following error.

    Error: Failed to lock variable "user::varFilePath" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

    also i have declared varFilePath readonly in script task.

  • Hi abhas

    I think you have declared a variable user:varFilePath in your script task as read only,change it to User:varFilePath.

    I have a sample project,if you want I'll send it to you

    Bye

    Rashmi

  • Thank you rashmi!

    Its working now.

    But can i do these by another way? Can i copy only those files from source to destination which are 90 days old only.

    currently i am copying all files from source to destination and then delete old files from destination. Instead i want to copy only new files.Is this possible?

    Thanks

    Abhas.

  • Hi Abhas

    Yes,You can copy only those files from source to destination which are 90 days old only.

    Do the same as i said, the only change you need in your programme is

    Use a for each loop container,which iterate through each file in SourceFolder

    Write the folloeing code in ScriptTask

    Public Sub Main()

    Dim SourceFilepath As String

    Dim destFolder As String

    Dim destFilePath As String

    Dim SourceFileName As String

    SourceFilepath = Dts.Variables("User::varFilePath").Value.ToString()

    destFolder = "D:\Test\Destination\"

    SourceFileName = SourceFilepath.Substring(SourceFilepath.LastIndexOf("\") + 1)

    destFilePath = destFolder & SourceFileName

    If File.Exists(SourceFilepath) = False Then

    Dts.TaskResult = Dts.Results.Failure

    Return

    End If

    Dim fileCreateDate As Date = File.GetCreationTime(SourceFilepath)

    Dim tsTimeSpan As TimeSpan

    Dim noOfDays As Integer

    tsTimeSpan = Now.Subtract(fileCreateDate)

    noOfDays = tsTimeSpan.Days

    If noOfDays <= 90 Then

    System.IO.File.Copy(SourceFilepath, destFilePath, True)

    Dts.TaskResult = Dts.Results.Failure

    Else

    Dts.TaskResult = Dts.Results.Success

    End If

    Dts.TaskResult = Dts.Results.Success

    End Sub

    Note :Change the path of destination folder "D:\Test\Destination\" before you run

    Hope this will solve your problem

    Incase any query let me know

    Regards

    Rashmi

  • Hi Rashmi,

    Thanks it works but not fit in my scenario.

    Actually all files are loaded at root level only.

    My source is:D:\FolderA

    in the same folder there are other 4 sub folders say

    D:\FolderA\test1

    D:\FolderA\test2

    D:\FolderA\test3

    D:\FolderA\test4

    and test 1 to test4 contains files.I want to copy as same scenario at the destination.i.e i want to put files from F:\FolderA\test1 and so on

    But above code copy all files at D:\FolderA\ level only.

    does it possible?

    thnaks

  • Hi abhas

    Yes ,it is possible to get the file from test1 through test4 .In your ForeachloopEditor select the connection tab and then select the checkbox Traverse Subfolders.

    Hope this will solve your problem

    Incase any query let me know

    Regards

    Rashmi

  • Hi rashmi,

    i already checked Traverse Subfolders but not working.

  • Hi abhas

    The last solution works fine.I have attached a sample project .Try it out

    In case any query let me know

    Regards

    Rashmi

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply