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.
-
The destination table is created if it does not exist already
-
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.
-
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.