Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Importing The Latest File In A Folder Via DTS


Importing The Latest File In A Folder Via DTS

Author
Message
David Jackson
David Jackson
Mr or Mrs. 500
Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)

Group: General Forum Members
Points: 501 Visits: 1879
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."
nefert
nefert
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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!
David Jackson
David Jackson
Mr or Mrs. 500
Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)

Group: General Forum Members
Points: 501 Visits: 1879
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 Smile
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."
nefert
nefert
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 4
Good sugestion! i already did that! Thanks a lot! have a nice day!Smile
BKW
BKW
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 232
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search