Knowledge.ToString()

Stop DTS Package Execution Conditionally Without Failure

There is no straight way in DTS to stop the package on a certain condition without having it fail. Suppose in DTS package is suppose to be executed everyday at 8:00AM. This package is looking for a file c:\abc.txt. If the file has not arrived, it should gracefully stop without reporting any error. If the file has arrived then process the file and report success. This situation is handled by a bit of programming. Write down the following code in ActiveX Script Task.

Dim fso, file1
Dim pkg
SET pkg = DTSGlobalVariables.Parent
 
Set fso = CreateObject("Scripting.FileSystemObject")
 
IF (fso.FileExists("c:\abc.txt")) THEN
    Msgbox "File exist. Continue"
    pkg.Steps("DTSStep_DTSActiveScriptTask_2").DisableStep=False
 
    Set fso= Nothing
 
ELSE
    Msgbox "Stop"
    pkg.Steps("DTSStep_DTSActiveScriptTask_2").DisableStep=True
END IF
set pkg=nothing
Main = DTSTaskExecResult_Success

What this does is disables the second step and hence the whole sequence of task is automatically disabled. so the Task1 will report success and DTS package execution will be completed without executing task 2 and onwards. You can download the dts package example and try to run it on your computer. It looks for file c:\abc.txt. If this file exists, it will run total 3 task with result SUCCESS. If the file is not found, it will run only Task1 with result SUCCESS.

Share

Comments

11 responses to “Stop DTS Package Execution Conditionally Without Failure”

  1. phaniraj Avatar
    phaniraj

    excellent article

  2. Alex Avatar
    Alex

    Cheers for this example. Helped me out!

  3. Anthony Avatar
    Anthony

    I can’t get this to work for me. I’m getting an error code:0, expected end of statement in the first line. Appreciate if someone can help or advise. I’m I missing something?

    Thanks.

  4. kanthi Avatar
    kanthi

    Thanks a lot Vishal. I have been searching about this for a long time.

  5. TG Avatar
    TG

    Vishal.

    This is awesome. I have a peer trying it for days but could not do it. I surfed and came to your blog…BINGO.

    Thanks a ton

  6. Jeff Avatar
    Jeff

    Thank you.

  7. Jen Avatar
    Jen

    This is perfect for my validation step.. Best.

  8. Developer Avatar
    Developer

    Perfect!
    Many thanks.. saved alot of time

  9. Arthi Avatar
    Arthi

    Great!!! Big help to find this………

  10. Alan Avatar
    Alan

    Thanks a lot, it’s perfect !

  11. Wooballoo Avatar
    Wooballoo

    Fantastic – saved a lot of time!!!!

Leave a Reply

Your email address will not be published. Required fields are marked *