SQL Package Active X Task (Stop Execution from within VBscript)

Posted on 2007-09-28
Last Modified: 2013-11-30
I have a SQL 2000 DTS package.

In side the package I have an Active X Task (VBScript).

It looks to see if a file exists.  

If Yes   Main =  DTSTaskExecResult_Success
  ***** want to stop procedure but return success to scheduled job.

In ohter words I have a package the is scheduled to run every day.  Some days we don't receive a file, so I don't want an error or failure, just to stop the pacage from running and exit successfully.

Is this possible?


Question by:Rog
    LVL 92

    Expert Comment

    by:Patrick Matthews
    Main =  DTSTaskExecResult_Success
    Dim fso, result
    Set fso = CreateObject("Scripting.FileSystemObject")
    result = fso.FileExists("path to file here")
    Set fso = Nothing
    If Not result Then Exit Sub

    'put rest of code here
    LVL 8

    Author Comment


    This is good and I do understandt this.  

    The code you provided does check for a file, but then I want to exit DTS Package with success not just exit the task.

    The reason for this is if a file is found I want to the dtspackage task with success and continue.

    LVL 75

    Accepted Solution

    The way to handle this is with a Workflow ActiveX script on the task.  Something like this:

    If file found then
          Main = DTSStepScriptResult_ExecuteTask
          Main = DTSStepScriptResult_DontExecuteTask
    End If

    In order to write code to the Workflow ActiveX script:
    1. Right click on the task.
    2. Select Workflow properties.
    3. Click on Options tab
    4. Click on "Use ActiveX Script"
    5. Select Properties.

    This code will execute BEFORE the task.
    LVL 8

    Author Comment

    I understand, What exactly does the  DTSStepScriptResult_DontExecuteTask

    Will is stop the package from running?

    Will is set the exit code to success?

    The problem I have is I can set Main = "Success" or "Failure" based on finding a file.

    Then take the workflow failure route, but then the job shows "Error"

    Basically I want the job to end as a success if a file is NOT found.

    If a file is found, I want the package to continue with success route executing other tasks or other items.


    LVL 75

    Expert Comment

    by:Anthony Perkins
    >>What exactly does the  DTSStepScriptResult_DontExecuteTask<<
    They are the constants used in Workflow ActiveX Scripts in much the same way that DTSTaskExecResult_Success and DTSTaskExecResult_Failure are used in the Task ActiveX Script

    >>Will is stop the package from running?<<

    >>Will is set the exit code to success?<<

    >>The problem I have is I can set Main = "Success" or "Failure" based on finding a file.<<
    I understand.  It will never work that way.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    754 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