Friday, December 04, 2009

Loop through files in a folder Use ActiveX

There are times when you need to process all files in a folder. SSIS gives us the While loop that can parse all files in a folder. However DTS requires a work around with ActiveX and the trusty FileSystemObject. Here is some code that parses through all the files in a folder and pops up a message box with the file name.

'**********************************************************************
' Visual Basic ActiveX Script
'**********************************************************************


Function Main()

'Set the target folder name
folder = "\\MyDrive\Myfolder\"

set fso = CreateObject("Scripting.fileSystemObject")
set fold = fso.getFolder(folder)

' Loop through the files in the folder
for each file in fold.files

' Pop up a Message Box with the file name
MsgBox( file.name)

' You can also set global variables with this option
'set DTSGlobalVariables("gv_FileName").Value = file.name

next

set fold = nothing
set fso = nothing

Main = DTSTaskExecResult_Success

End Function

Thats it! This script is also handy for files that don't have a predefined name, such as filenames that contain a timestamp (ie Filename_YYYYMMDDHHMMSS.csv)