locked
how to change file attributes for all files in the folder using ssis. RRS feed

  • Question

  • Hi,

    I am trying to copy files and folders from one location to another.This is a repeatitive process and runs everyday. I am using file system task for this.  
    The problem I am having is in setting the attributes on the destination files,folders.

    The package runs fine for the first time where it copies all the files and folders to the destination for the first time and sets some of the destination files to readonly (like.bat file).So,When the package runs the second time, it fails because it cannot overwrite the files. 

    I used a script that would set the ISreadonly option to false. This does it only for the files under main directory.
    the input variable is the path of the main directory (example c:\test\ )

    So, I am trying to figure out a way to set the attributes of  all files including the ones under subfolders to False.

    Thank you in advance.

    Wednesday, August 8, 2012 6:16 PM

Answers

  • you can execute the DOS command in the root directory. It will remove all Readonly attribute from the files and directories.

    In SSIS, You can use Execute Process Task to execute this command

    attrib -r  /d /s

    In your scenario,

    Setup Execute Process Task like given below

    current working directory is c:\test\

    Command is : Attrib

    Argument is : "-r  /d /s"

    • Edited by [Ayyappan] Wednesday, August 8, 2012 8:18 PM
    • Proposed as answer by [Ayyappan] Wednesday, August 8, 2012 9:47 PM
    • Marked as answer by A208 Wednesday, August 8, 2012 10:03 PM
    Wednesday, August 8, 2012 8:15 PM
  • Use that source path variable name in the WorkingDirectory property expression.

    Thanks Ayyappan Thangaraj, http://SQLServerRider.wordpress.com

    • Proposed as answer by [Ayyappan] Wednesday, August 8, 2012 9:46 PM
    • Marked as answer by A208 Wednesday, August 8, 2012 10:03 PM
    Wednesday, August 8, 2012 9:16 PM

All replies

  • The VB code below put into a Script Task should do the trick (you may need some customization to it), the main point is you cycle through the sub-folders:

    Function ChangeFileAttributes(strPath As String, _
                                Optional lngSetAttr As FileAttribute, _
                                Optional lngRemoveAttr As FileAttribute, _
                                Optional blnRecursive As Boolean) As Boolean
       
       ' This function takes a directory path, a value specifying file
       ' attributes to be set, a value specifying file attributes to be
       ' removed, and a flag that indicates whether it should be called
       ' recursively. It returns True unless an error occurs.
       
       Dim fsoSysObj      As FileSystemObject
       Dim fdrFolder      As Folder
       Dim fdrSubFolder   As Folder
       Dim filFile        As File
       
       ' Return new FileSystemObject.
       Set fsoSysObj = New FileSystemObject
       
       On Error Resume Next
       ' Get folder.
       Set fdrFolder = fsoSysObj.GetFolder(strPath)
       If Err <> 0 Then
          ' Incorrect path.
          ChangeFileAttributes = False
          GoTo ChangeFileAttributes_End
       End If
       On Error GoTo 0
       
       ' If caller passed in attribute to set, set for all.
       If lngSetAttr Then
          For Each filFile In fdrFolder.Files
             If Not (filFile.Attributes And lngSetAttr) Then
                filFile.Attributes = filFile.Attributes Or lngSetAttr
             End If
          Next
       End If
       
       ' If caller passed in attribute to remove, remove for all.
       If lngRemoveAttr Then
          For Each filFile In fdrFolder.Files
             If (filFile.Attributes And lngRemoveAttr) Then
                filFile.Attributes = filFile.Attributes - lngRemoveAttr
             End If
          Next
       End If
       
       ' If caller has set blnRecursive argument to True, then call
       ' function recursively.
       If blnRecursive Then
          ' Loop through subfolders.
          For Each fdrSubFolder In fdrFolder.SubFolders
             ' Call function with subfolder path.
             ChangeFileAttributes fdrSubFolder.Path, lngSetAttr, lngRemoveAttr, True
          Next
       End If
       ChangeFileAttributes = True
    
    ChangeFileAttributes_End:
       Exit Function
    End Function


    Arthur My Blog

    Wednesday, August 8, 2012 6:25 PM
  • Hi arthur, I tired this but does not seem to work. 

    Imports System
    Imports System.Data
    Imports System.Math
    Imports Microsoft.SqlServer.Dts.Runtime
    
    <System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
    <System.CLSCompliantAttribute(False)> _
    Partial Public Class ScriptMain
    	Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    
    	Enum ScriptResults
    		Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
    		Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    	End Enum
    	
        Public Sub Main()
            Dim SourcePath As String
            SourcePath = CStr(Dts.Variables("User::Destination111").Value)
    
    
            If IsFolderReadOnly(SourcePath) Then
                Dim oDir As New System.IO.DirectoryInfo(SourcePath)
                oDir.Attributes = oDir.Attributes And Not IO.FileAttributes.ReadOnly
            End If
        End Sub
        Private Function IsFolderReadOnly(ByVal Folder As String) As Boolean
            Dim oDir As New System.IO.DirectoryInfo(Folder)
            Return ((oDir.Attributes And IO.FileAttributes.ReadOnly) > 0)
        End Function
    
    End Class  

    Do you see any thing wrong here? the task is getting executed successfully but does not reset the read only attribute.

    I got the above code from http://social.msdn.microsoft.com/Forums/en/vblanguage/thread/cb75ea00-f9c1-41e5-ac8e-296c302827a4
    • Edited by A208 Wednesday, August 8, 2012 6:45 PM
    Wednesday, August 8, 2012 6:32 PM
  • Try removing the attribute, then changing it.

    Is that on your local machine that does not work or everywhere?

    In Win 7 sometimes it just does not get applied visually, same in Vista.


    Arthur My Blog

    Wednesday, August 8, 2012 6:55 PM
  •  I am trying it on my local machine(windows 7). the package has execute sql task which gets the rows from the table and passes them to the foreach loop container where script task executes. Example : execute sql task will return result set like "c:/test1"  and passed to for each loop, now the script task to loop through the folderss. what do you mean by remove the attribute and change. Thanks.
    Wednesday, August 8, 2012 7:27 PM
  • Try on the server now.

    I asked in code to remove the read only attribute 1st. Would that work?


    Arthur My Blog

    Wednesday, August 8, 2012 7:30 PM
  • you can execute the DOS command in the root directory. It will remove all Readonly attribute from the files and directories.

    In SSIS, You can use Execute Process Task to execute this command

    attrib -r  /d /s

    In your scenario,

    Setup Execute Process Task like given below

    current working directory is c:\test\

    Command is : Attrib

    Argument is : "-r  /d /s"

    • Edited by [Ayyappan] Wednesday, August 8, 2012 8:18 PM
    • Proposed as answer by [Ayyappan] Wednesday, August 8, 2012 9:47 PM
    • Marked as answer by A208 Wednesday, August 8, 2012 10:03 PM
    Wednesday, August 8, 2012 8:15 PM
  • HI Ayyapan,

     I tested this thing in dos and it worked . to configure it in ssis, I do not see a prompt for Attrib. what should i use for executable prompt. kindly let me know. thanks,

    Wednesday, August 8, 2012 8:38 PM
  • Please use Attrib in the executable property.


    Thanks Ayyappan Thangaraj, http://SQLServerRider.wordpress.com

    Wednesday, August 8, 2012 9:02 PM
  • Thanks that works. what if my input is dynamic coming from a sql query.  user::destination111 is a varible that has gets sourcepath from a table example  E:/test1 ..it did not work when I made the path dynamic. Thanks.below is the properties window of my process task


    • Edited by A208 Wednesday, August 8, 2012 9:13 PM
    Wednesday, August 8, 2012 9:10 PM
  • Use that source path variable name in the WorkingDirectory property expression.

    Thanks Ayyappan Thangaraj, http://SQLServerRider.wordpress.com

    • Proposed as answer by [Ayyappan] Wednesday, August 8, 2012 9:46 PM
    • Marked as answer by A208 Wednesday, August 8, 2012 10:03 PM
    Wednesday, August 8, 2012 9:16 PM
  •  Thanks you so much..It's working. Does the dos command remove readonly property even after it was copied down?? Also, many people are using .net code to accomplish this task but the dos command looks very easy step. is there any reason why people do not use dos command to accomplish this task?? Thanks for your help.
    Wednesday, August 8, 2012 9:50 PM
  • Attrib (Atrribute) is a command use to add or remove file attribute in dos. I like to use simplest soultion whereever i can do. SSIS does allow us to execute external command and in easy way. .Net does the same thing but requires some addition line of code to accomplish this task. I do use .net script to accomplish this task if I were developing a .net application.


    Thanks Ayyappan Thangaraj, http://SQLServerRider.wordpress.com

    Thursday, August 9, 2012 4:10 PM