IF OBJECT_ID('dtsloop') IS NULL

BEGIN

CREATE TABLE [dtsloop] (
[Col001] varchar (255) NULL, 
[Col002] varchar (255) NULL, 
[Col003] varchar (255) NULL )

END 

' 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

' 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

' 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 of there are more
	' than 1 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 is more than 1 file 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

Col001

Col001

Col002

Col002

Col003

Col003

' 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

' 246 (Finished)
Option Explicit

Function Main()

	MSGBOX "Package has Completed."

	Main = DTSTaskExecResult_Success
End Function