locked
delete files from remote server RRS feed

  • Question

  • Hi,

    How i have a folder in a server say xyz containing pdf files.

    Now i have to schedule a job on a different server say ABC that will delete some of the pdf files.

    How can i achieve this in a package.Also,XYZ is not an ftp server


    Thanks and regards, Rishabh
    Tuesday, January 11, 2011 4:30 AM

Answers

  • Hello Rishabh,

    If both servers (I assume they are running Windows OS) are within the same domain or friend (linked) domains then with proper user rights you can issue a File System Task from your SSIS package similar to http://www.sql-server-performance.com/articles/biz/file_system_task_ssis_p1.aspx

    A more close to what you need example is http://www.codeproject.com/Articles/81607/Deleting-Files-using-File-System-Task-in-SSIS.aspx

    If the user has no admin rights, then this folder may need to be shared and the proper permissions granted to the user account executing the package.

    To schedule the SSIS package there are two options: SQL Server Agent and a bat file calling the package using DTExec.

    If the boxes are not on the same domain or running different OSs, other approaches are necessary, but we need to know the specifics.


    Arthur My Blog
    By: TwitterButtons.com
    Tuesday, January 11, 2011 4:51 AM
  • can you share xyz folder and set permission and then view it by another server?

    if yes, you just need a file system task to delete files in the shared folder

     


    http://www.rad.pasfu.com
    Tuesday, January 11, 2011 6:45 AM
  • this script is for deleting all folders which created more than 2 hours before current time:

     

    Public Sub Main()
        Dim dirArray As String() = System.IO.Directory.GetDirectories("C:\test\")
        For Each dirStr As String In dirArray
          If (System.IO.Directory.GetCreationTime(dirStr) < DateTime.Now.AddHours(-2)) Then
            System.IO.Directory.Delete(dirStr, True)
          End If
        Next
    
    		Dts.TaskResult = ScriptResults.Success
    	End Sub
    


    http://www.rad.pasfu.com
    Tuesday, January 11, 2011 7:10 AM
  • Hi Rishabh,

    We just need to change the path in Reza's sample code to be a shared folder. We can use the UAC path to access the folder like \\xyz\foldername

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    • Marked as answer by Rishabh K Friday, January 14, 2011 3:27 AM
    Friday, January 14, 2011 3:11 AM

All replies

  • Hello Rishabh,

    If both servers (I assume they are running Windows OS) are within the same domain or friend (linked) domains then with proper user rights you can issue a File System Task from your SSIS package similar to http://www.sql-server-performance.com/articles/biz/file_system_task_ssis_p1.aspx

    A more close to what you need example is http://www.codeproject.com/Articles/81607/Deleting-Files-using-File-System-Task-in-SSIS.aspx

    If the user has no admin rights, then this folder may need to be shared and the proper permissions granted to the user account executing the package.

    To schedule the SSIS package there are two options: SQL Server Agent and a bat file calling the package using DTExec.

    If the boxes are not on the same domain or running different OSs, other approaches are necessary, but we need to know the specifics.


    Arthur My Blog
    By: TwitterButtons.com
    Tuesday, January 11, 2011 4:51 AM

  • Hi Arthur,

    Thanks for your reply but can i achieve this in script task.


    Thanks and regards, Rishabh
    Tuesday, January 11, 2011 5:42 AM
  • and how will i be able to access the directory on a different server without setting any connection .

    Thanks and regards, Rishabh
    Tuesday, January 11, 2011 5:44 AM
  • Hi,

    I forgot to add one more thing.the files that are to be deleted are present on server that does not have any sql server installed on it.


    Thanks and regards, Rishabh
    Tuesday, January 11, 2011 6:02 AM
  • can you share xyz folder and set permission and then view it by another server?

    if yes, you just need a file system task to delete files in the shared folder

     


    http://www.rad.pasfu.com
    Tuesday, January 11, 2011 6:45 AM
  • Hi Reza,

    Ok i understand this but this is not the only thing i want to do .

    I have to run a job which when runned at 5 A.M will only delete folders created at 3 a.m and when the job is run at 12 noon it will delete

    folders created at 10 a.m.

    I think i can achieve this in script task only but i have zero exp in vb.net.

    So,How can i achieve this??


    Thanks and regards, Rishabh
    Tuesday, January 11, 2011 6:51 AM
  • this script is for deleting all folders which created more than 2 hours before current time:

     

    Public Sub Main()
        Dim dirArray As String() = System.IO.Directory.GetDirectories("C:\test\")
        For Each dirStr As String In dirArray
          If (System.IO.Directory.GetCreationTime(dirStr) < DateTime.Now.AddHours(-2)) Then
            System.IO.Directory.Delete(dirStr, True)
          End If
        Next
    
    		Dts.TaskResult = ScriptResults.Success
    	End Sub
    


    http://www.rad.pasfu.com
    Tuesday, January 11, 2011 7:10 AM
  • Hi Rishabh,

    We just need to change the path in Reza's sample code to be a shared folder. We can use the UAC path to access the folder like \\xyz\foldername

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    • Marked as answer by Rishabh K Friday, January 14, 2011 3:27 AM
    Friday, January 14, 2011 3:11 AM
  • Hi All,

    On running as suggested by you all.

    i got this error:

    Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.IO.IOException: Logon failure: unknown user name or bad password.

       at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
       at System.IO.Directory.InternalGetFileDirectoryNames(String path, String userPathOriginal, String searchPattern, Boolean includeFiles, Boolean includeDirs, SearchOption searchOption)
       at System.IO.Directory.GetDirectories(String path, String searchPattern, SearchOption searchOption)
       at System.IO.Directory.GetDirectories(String path)
       at ST_1dd540f4f4d047f3b67b092f0e52d4bf.vbproj.ScriptMain.Main()
       --- End of inner exception stack trace ---
       at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
       at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
       at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
       at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
       at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
       at System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture)
       at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

     


    Thanks and regards, Rishabh
    Friday, January 14, 2011 3:44 AM
  • Hi,

    Thanks all, I was able to do it.The above problem occurred since i was not using network path.


    Thanks and regards, Rishabh
    Friday, January 14, 2011 4:07 AM