Bandwidth Monitor
SQLDTS.comSQLIS.comSQLIS Wiki
Read File Transformation
By Allan Mitchell
Version 2000
Level Beginner
Page 1 | Page 2 | Page 3 | Page 4

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
Page 1 | Page 2 | Page 3 | Page 4