I've created a simple DTS Package in which there is an activeX script where I check if a file exists. If it there a "0" is returned and the DTS package continues on the "OnSuccess" Workflow. If it's not there the script returns a "1" and the package then goes and runs the "On Failure" branch of the package.
Main = DTSTaskExecResult_Failure
Set filesys = CreateObject("Scripting.FileSystemObject")
If filesys.FileExists("\\l1338abc\e$\test\test_DTS.txt") Then
Main = DTSTaskExecResult_Success
When I run this directly under DTS it works fine. When I schedule it the job runs through as expected, but the job is displayed in Enterprise manager as failed. It hasn't really failed, it just returned the value of one and completed as programmed.
My problem is I need to have several steps in this Job and this particular DTS package is the first step. As a job it interprets the "1" as an error and it steps out with an error message. I could just set the job to continue on error. If I do that, I won't see if a "real" error takes place.
How can I get SQL Server to accept this "1" not as an error but as a return code and continue on with the following steps?