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)
