Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Importing The Latest File In A Folder Via DTS Expand / Collapse
Author
Message
Posted Tuesday, February 19, 2008 7:20 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, July 12, 2014 6:44 AM
Points: 441, Visits: 1,799
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."
Post #457369
Posted Wednesday, February 20, 2008 2:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, March 2, 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!
Post #458233
Posted Sunday, March 2, 2008 10:48 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, July 12, 2014 6:44 AM
Points: 441, Visits: 1,799
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."
Post #462929
Posted Sunday, March 2, 2008 2:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, March 2, 2008 2:39 PM
Points: 3, Visits: 4
Good sugestion! i already did that! Thanks a lot! have a nice day!:)
Post #462950
Posted Wednesday, September 3, 2008 3:36 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 4, 2014 3:59 PM
Points: 49, Visits: 185
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
Post #563435
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse