?
Solved

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

Posted on 2004-11-25
12
Medium Priority
?
322 Views
Last Modified: 2013-11-30
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?
0
Comment
Question by:DSchat
  • 6
  • 2
9 Comments
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 12675176
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..
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 12675709
>>
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\ ??

0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 12676696
Hilaire:

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?
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:DSchat
ID: 12679511
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.
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 12681213
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?
0
 

Author Comment

by:DSchat
ID: 12681487
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.
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 12684279
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.
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 12684329
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
      ELSE
         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.
0
 
LVL 13

Accepted Solution

by:
MikeWalsh earned 260 total points
ID: 12684340
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.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

850 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