The way we are going to approach this is as follows.

• Given a location as a Global Variable loop over that directory

• As we encounter a file assign the name to global variables

• Use the Global Variable in an ExecuteSQL task as a parameter

• Move the file to a loading directory and loop back around the until we have no more files

• We then move on to importing those files using the Read File transformation.

Phase 1:.

Function Main()
	Dim pkg
	Dim stpEnterLoop
	Dim stpFinished

	set pkg = DTSGlobalVariables.Parent
	set stpEnterLoop = pkg.steps("DTSStep_DTSExecuteSQLTask_1")
	set stpFinished = pkg.Steps("DTSStep_DTSDataPumpTask_1")

	' 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
	else
		stpEnterLoop.DisableStep =True
		stpFinished.DisableStep = False
	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("SourceDirectory").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
		'This is the name we will use for loading and pass to ExecuteSQL task
		DTSGlobalVariables("FileNameUsedForLoading").Value = DTSGlobalVariables("Archivedirectory").Value & fil.Name
		'This is the name of the file we will be moving to the load location
		DTSGlobalVariables("NameOfFile").Value = fil.Path
		ShouldILoop = CBool(True)
	Next

	else
		ShouldILoop = CBool(False)
	End if

set fso = nothing

End Function