DTSStepScriptResult_DontExecuteTask return status issue

Hi,

I'm processing a number of files from a folder.If the first file has a size 0, I want to stop the task but wish to process the other files. I'm using the following code in the Workflow properties.
---------------------------------------------------------
Function Main()

        Dim oFSO, sFileName1,oFile

        ' Get filename from the Connections
        sFilename1 = DTSGlobalVariables.Parent.Connections("InputFile").DataSource

        Set oFSO = CreateObject("Scripting.FileSystemObject")

    Set oFile = oFSO.GetFile(sFilename1)

    If oFile.Size > 0 Then

                Main = DTSStepScriptResult_ExecuteTask
        Else
                Main = DTSStepScriptResult_DontExecuteTask
        End If

    Set oFile = Nothing
    Set oFSO = Nothing

End Function
--------------------------------------------------------
It detects the  first file size is 0 and therefore does
not run the task which is fine but 'DTSStepScriptResult_DontExecuteTask' seems to be returning a 'failure' status and therefore the rest of the files from the folder are not being picked up.

Is there any workaround or a way to force DTSStepScriptResult_DontExecuteTask to return a 'success' status which will enable the other files to be processed?

Thanks.
PerjeyAsked:
Who is Participating?
 
Anthony PerkinsCommented:
Assuming you are using something like this article:
Looping, Importing and Archiving
http://www.sqldts.com/default.aspx?246

Than all you have to do is make the following change in the ShouldLoop 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
            If fil.Size > 0 Then            ' Add this
                  DTSGlobalVariables("gv_FileFullName").Value = fil.path
                  ShouldILoop = CBool(True)
                  Exit For            ' No need to go any further
            End If
            ' ShouldILoop = CBool(True)     ' Comment this out
      Next
else
      ShouldILoop = CBool(False)
End if

End Function
0
 
SoftEng007Commented:
create a dts package that loops thru all of the files only.
have it delete all of the 0 length files from then directory.
then run your processing package.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.