DTS Package with AktiveX Script doesn't work as a scheduled job

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.

Function Main()
      Main = DTSTaskExecResult_Failure
      dim filesys
      Set filesys = CreateObject("Scripting.FileSystemObject")
      If filesys.FileExists("\\l1338abc\e$\test\test_DTS.txt") Then
            Main = DTSTaskExecResult_Success
      End If
End Function

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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

One quick thing you could do without much thought added to it is split the DTS package into two packages at the point of the activex task. Then just modify the job precedence. If the file is there and the package reports success you move on to the step that you want to start at if the file exists. If not then you go to the job step you want that has the file. Of course if your pakcage with the activex script really does fail your job will continue on..
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. <<

When you use DTS on your workstation the code is executed using your own credentials.
When you schedule a job the code is run under the account that runs SQL Server Agent.

Can you check that this account has sufficient rights to read on the remote directory \\l1338abc\e$\test\ ??


I was thinking that at first, but it sounds like the problem is that the activex task is reporting a failure which is OK, because the failure workflow is doing something when it fails, but when one task reports a failure the package believes it faile dbecause "one or more tasks has failed" during the execution of the package. Is that correct?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

DSchatAuthor Commented:
Hi Mike,

exactly, "one or more tasks has failed" is the situation. The problem is the way the job control in SQL interprets the "failure". It isn't an error, it is a query that returns more or less a yes or no answer. Here the no is found to be an error.

- Hilaire: as far as rights are concern, this particular test I'm doing is all local and my UserID is in the Administrator group on my computer. In our Integration enviroment, where this behavior was first noticed, I was running also with an Admin account. Also in my local testing, I watch the file in another window and can see that items are created and dropped from the DTS.

We just need to figure out how to get job control to understand that a "no" answer is not an error and that nothing has failed.
As long as the no condition is being checked and handled with the DTSEXectResult_Failure method that activex script will always report an error to the package, I believe. I don't know what is being run after that, but you could always make your file check query update a column in a table, and then you can have all of your future tasks run or not based on the value of that column.

You could also have the file polling be a separate process in a separate DTS package that executes that the beginning of a job.

How much and what are you doing in the rest of that package/job if the file is NOT there?
DSchatAuthor Commented:
If the file is nonexistent another file is deleted (inside the DTS). The DTS is a part of a larger job with several steps. I've now built a small workaround in the job where a message is sent when the step fails, but the job doesn't break off. I still have the the same problem that I would have in your first suggestion, that if a real error occurs, the job continues. I just need in any case to check the results when I get a message.
Unfortunately I can't think of anything else at this moment. I will keep toying around with this in my head, I'll post back if I think of anything else other than my workarounds above.
I found this:

Turning a Step On and Off
The following example, written in Microsoft Visual Basic® Scripting Edition (VBScript), uses an ActiveX script to turn a step on or off based on the existence of a file:

Function Main()

      Dim fso 'File system object
      Set fso = CreateObject("Scripting.FileSystemObject")
      IF (fso.FileExists("C:\temp\download.tmp")) THEN
         Main = DTSStepScriptResult_ExecuteTask
         Main = DTSStepScriptResult_DontExecuteTask
      END IF

End Function

Maybe rather than return a failure of the object doesn't exist, you can control the precedence of your execution by either turning a step on or off, or calling that step direct.. Check out: http://msdn.microsoft.com/library/en-us/dtssql/dts_elemwkflow_0793.asp for more.
From www.sqldts.com (a great resource by the way.. i check it often for DTS related info):

The problem with using an ActiveX Script Task is that because the task fails, so does the package. This is often inappropriate, for example you may have package scheduled every 15 minutes that imports a file. This file is only produced when certain conditions have been met on an external system, and the absence of the file is by no means cause for concern. This is where you can use the power of an ActiveX Workflow Script. Workflow scripts use the DTSStepScriptResult constants, one of which is DTSStepScriptResult_DontExecuteTask. As you'd expect from the name, the task associated with this step does not execute, but importantly it does not raise any errors either.

To use an ActiveX workflow script, select the task that must be prevented from executing when the file is missing. Right-click Workflow Properties, or Workflow followed by Properties as appropriate. On the Options tab select "Use ActiveX Script", and then click Properties. You will now need to code the checking mechanism and return the appropriate result. DTSStepScriptResult_ExecuteTask allows the task to execute and DTSStepScriptResult_DontExecuteTask blocks the workflow path and prevents the task from executing.

So you should theoretically be able to add this to the scripts you want to only run if the file does not exist, if the file exists don't run the task. If the file doesn't exist do run the task. Or you could always do it in the reverse.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.