One of the most common things asked for by people using DTS is the ability to loop through a directory and process files along the way. This is how I do it. Although it may look a little complicated for what is quite an easy task it isn't trust me..

There are 7 main parts to this package.

1. The destination table is created if it does not exist already
2. We define our GlobalVariables. We also need to check and make sure that the directories for the file location and the archiving location are correct.
3. If any of the directories are incorrect then we exit the package
Here we begin to do our loop. We loop through the files in the folder and pass the name of the file to the text file connection
This is the pump into a SQL Server table
Here we loop back around to pick up the next file
When there are no more files to pick up we exit.

Here is the code for all the ActiveX scripts we have defined.

2 DefineTheGVs

' 246 (DefineTheGVs)
Option Explicit

Function Main()

	Dim fso
	Dim fold
	Dim pkg
	Dim stpContinuePkg
	Dim stpExitbadDirectory

	' First thing we need to do is to check if our directories are valid.

	SET pkg = DTSGlobalVariables.Parent

	SET stpContinuePkg = pkg.Steps("DTSStep_DTSActiveScriptTask_3")
	SET stpExitBadDirectory = pkg.Steps("DTSStep_DTSActiveScriptTask_2")
	
	DTSGlobalVariables("gv_FileCheckErrors").Value = ""

	'We use the FileSystemObject to do our
	'Folder manipulation

	set fso = CREATEOBJECT("Scripting.FileSystemObject")


	'Here we check to make sure the Source folder for the files exists

	if fso.FolderExists(DTSGlobalVariables("gv_FileLocation").Value) <>  "True" then
	DTSGlobalVariables("gv_FileCheckErrors").Value = CSTR(DTSGlobalVariables("gv_FileCheckErrors").Value) &_
		 " " & "Source File directory Not Found"
	end if

	'Here we check to make sure the Archive folder for the files exists

	if fso.FolderExists(DTSGlobalVariables("gv_ArchiveLocation").Value)  <>  "True" then
	DTSGlobalVariables("gv_FileCheckErrors").Value = CSTR(DTSGlobalVariables("gv_FileCheckErrors").Value) &_
		 " " & "Archive File directory Not Found"
	end if

	'We predefined the GlobalVariable gv_FileCheckErrors = "" which
	'has a length of 2 so we check to see if it has expanded.  If it has then we
	'know we had an error and we disable the step that would
	'allow us to continue in the package and enable the step
	'that takes us out and handles the errors we encountered

	If len(DTSGlobalVariables("gv_FileCheckErrors").Value)  > 2 Then
		stpContinuePkg.DisableStep = True
		stpExitBadDirectory.DisableStep = False
	Else
		stpContinuePkg.DisableStep = False
		stpExitBadDirectory.DisableStep = True

	end if

	Main = DTSTaskExecResult_Success
End Function

3 Bad Directories

' 246 (Bad Directories)
Option Explicit

Function Main()

	Msgbox "You had a Bad Direcory or two please consult: " &_
	DTSGlobalVariables("gv_FileCheckErrors").Value

	Main = DTSTaskExecResult_Success
End Function

4 Begin Loop

' 246 (Begin Loop)
Option Explicit

Function Main()

	Dim pkg
	Dim  conTextFile 
	Dim stpEnterLoop
	Dim stpFinished

	set pkg = DTSGlobalVariables.Parent
	set stpEnterLoop = pkg.Steps("DTSStep_DTSDataPumpTask_1")
	set stpFinished = pkg.Steps("DTSStep_DTSActiveScriptTask_5")
	set conTextFile = pkg.Connections("Text File (Source)")

	' We want to continue with the loop only if there is
	' one or more text file in the directory. If the function ShouldILoop
	' returns true then we disable the step that takes us out of the package
	' and continue processing

	if ShouldILoop = True then
		stpEnterLoop.DisableStep = False
		stpFinished.DisableStep = True
		conTextFile.DataSource = DTSGlobalVariables("gv_FileFullName").Value
		stpEnterLoop.ExecutionStatus = DTSStepExecStat_Waiting
	else
		stpEnterLoop.DisableStep =True
		stpFinished.DisableStep = False
		stpFinished.ExecutionStatus = DTSStepExecStat_Waiting
	End if

	Main = DTSTaskExecResult_Success
End Function


Function ShouldILoop

	Dim fso
	Dim fil	
	Dim fold 
	Dim pkg
	Dim counter

	
	set pkg = DTSGlobalVariables.Parent
	set fso = CREATEOBJECT("Scripting.FileSystemObject")
	
	set fold = fso.GetFolder(DTSGlobalVariables("gv_FileLocation").Value)

	counter = fold.files.count

	' So long as there are text files in the directory carry on

	if  counter >= 1  then

	for each fil in fold.Files
		DTSGlobalVariables("gv_FileFullName").Value = fil.path
		ShouldILoop = CBool(True)
	Next

	else
		ShouldILoop = CBool(False)
	End if

End Function

6 Loop Around

' 246 (Loop Around)
Option Explicit

Function Main()

	Dim pkg
	Dim stpbegin
	Dim fso
	Dim fil
	Dim fold

	set pkg = DTSGlobalVariables.Parent
	set stpbegin = pkg.Steps("DTSStep_DTSActiveScriptTask_3")
	
	set fso = CREATEOBJECT("Scripting.FileSystemObject")
	
	'The trick to looping in DTS is to set the step at the start of the loop to an execution status of waiting

	stpbegin.ExecutionStatus = DTSStepExecStat_Waiting

	'This is how we do our archiving.  We use the FileSystemObject to move
	'the file to another directory
	'I extend this even further in my packages and zip the files up as well.
	'I do this using the command line zipping tool from PKWare
	
	fso.MoveFile  DTSGlobalVariables("gv_FileFullName").Value ,DTSGlobalVariables("gv_ArchiveLocation").Value

	Main = DTSTaskExecResult_Success
End Function

7 Finished

Option Explicit

Function Main()

	MSGBOX "Package has Completed."

	Main = DTSTaskExecResult_Success
End Function

You can download a sample package, which should illustrate this method better than any description. The package has a password of "sqldts" to ensure it is compatible across all versions of SQL Server and all service packs levels.