DTSStepScriptResult_DontExecuteTask  return status issue

Posted on 2006-03-24
Last Modified: 2007-12-19

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
                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?

Question by:Perjey
    LVL 9

    Expert Comment

    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.
    LVL 75

    Accepted Solution

    Assuming you are using something like this article:
    Looping, Importing and Archiving

    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
          ShouldILoop = CBool(False)
    End if

    End Function

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now