|
|
|
SSC-Addicted
      
Group: General Forum Members
Last Login: Friday, March 08, 2013 11:15 AM
Points: 440,
Visits: 1,785
|
|
I would do this by iterating through the files collection of the folder object, and moving them after processing into a 'Processed' Folder. Is that possible?
If none of the above makes sense, reply and I'll see if I can code it up for you.
Dave J
http://glossopian.co.uk/ "I don't know what I don't know."
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Sunday, March 02, 2008 2:39 PM
Points: 3,
Visits: 4
|
|
I guess if you write and example it will be more understandable for me or anyone else who read this forum. thanks a lot, have a good day!
|
|
|
|
|
SSC-Addicted
      
Group: General Forum Members
Last Login: Friday, March 08, 2013 11:15 AM
Points: 440,
Visits: 1,785
|
|
First, sorry this has taken a while to get back to you and post.
Second, this is a function taken from a vbs script, but it should work in a DTS package ActiveX step, though I haven't tested it. (Any references to WScript will need replacing, use Global Variables in the DTS Package).
Thirdly, it's not complete, but hopefully will be clear enough so as to be adaptable to do what you want to do.
Lastly, it is a recursive function. It's power is in it calls itself.
Dim oShell, oFS, sScript, sScriptPath
'Create a Shell and FileSystem Object - always useful :) Set oShell = WScript.CreateObject("WScript.Shell") Set oFS = CreateObject("Scripting.FileSystemObject") sScript = Wscript.ScriptFullName 'Name, including the path, of this script sScriptPath = oFS.GetAbsolutePathName(sScript & "\..") 'Path to this script
folderPath = "C:\TestTiffFiles" ' will need changing (Global Variable?) FileToLogTo = "ProcessedFiles.log" ' as will this
LogAction "Started processing from " & folderPath 'call to a sub routine, see below
If (oFS.FolderExists(folderPath)) Then ForEachFolder folderPath, True 'call to the recursive function End If
' End of script
'---------------------------------------------------------------------- Private Sub ForEachFolder(ByVal Source_Folder, doSubFolders) '---------------------------------------------------------------------- 'The second argument allow you not to use the recursivity. Dim fso, f, file, fileCollection, prefix, ext, pos
Dim oFolder, aFolders, FolderCollection Dim sFilename, oFile
Set aFolders = oFS.GetFolder(Source_Folder) Set FolderCollection = aFolders.SubFolders
'will not do any files in any folders that contain the word Processed pos = InStr(1,Source_Folder, "Processed",1) 'case in-sensitive match If pos > 0 then 'LogAction "NOT processing from " & Source_Folder Exit Sub end if
Set f = oFS.GetFolder(Source_Folder) Set fileCollection = f.Files
For Each file in fileCollection ' Do something with each file here by passing the file object to a sub ProcessFile file Next
If doSubFolders Then ' This bit calls this sub for each folder in the root folder For Each oFolder in FolderCollection Call ForEachFolder(oFS.GetAbsolutePathName(Source_Folder & "\" & oFolder.Name&"\"),doSubFolders) Next End If
Set oFile = Nothing Set aFolders = Nothing Set FolderCollection = Nothing
End Sub
'---------------------------------------------------------------------- Sub ProcessFile (file) '---------------------------------------------------------------------- 'place holder sub, place your processing for each file here 'After processing, use the oFS (filesystem) object to move the file somewhere you won't process again (Processed Folder)
End Sub
'---------------------------------------------------------------------- Sub LogAction (Entry) 'Helper Sub '---------------------------------------------------------------------- Dim f, LogFile 'msgbox Entry LogFile = ExecutingFrom & fileToLogTo
Const ForReading = 1, ForWriting = 2, ForAppending = 8
On Error Resume Next set f = oFS.OpenTextFile(LogFile, ForAppending, True) f.WriteLine "[ " & Now & " ] - " & Entry f.close
On Error Goto 0 set f = Nothing
End Sub
'---------------------------------------------------------------------- Function ExecutingFrom() 'Helper Function '---------------------------------------------------------------------- Dim ScriptPath
ScriptPath=Left(wscript.scriptfullname, _ Len(wscript.scriptfullname)-Len(wscript.scriptname)) ''will need changing to a global variaqble
If Right(ScriptPath,1) <> "\" Then ScriptPath = ScriptPath & "\" End If
ExecutingFrom = ScriptPath
End Function
HTH
Dave J
http://glossopian.co.uk/ "I don't know what I don't know."
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Sunday, March 02, 2008 2:39 PM
Points: 3,
Visits: 4
|
|
| Good sugestion! i already did that! Thanks a lot! have a nice day!:)
|
|
|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Wednesday, February 06, 2013 12:20 PM
Points: 49,
Visits: 183
|
|
Anyone got a way to get the last excel file using SSIS? I currently have a job that gets the last file in a folder like: file003 dated 09/03/2008 file002 dated 09/02/2008 file001 dared 09/01/2008
I need file003 to update table in DB.
I used this in SQL2000 as DTS and tried to convert it but didn't like the way it did it and would like to do it cleaner in SSIS
Changinagain
|
|
|
|