Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jsiedliski
Yak Posting Veteran
61 Posts |
 Posted - 2003-09-24 : 15:04:29
|
| I created a DTS package which has an ActiveX Script Task. The VBScript runs properly outside the DTS environment, but when I execute the DTS package, I get the following error:"Invalid Task Result Value"Here is what I found in BOl - I think the answer lies here (with the DTS Step Exec Stats):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 IFEnd FunctionCan someone help explain? More specifically, how do I get the activeX Task to either report Sucess or Failure in order to execute (or skip) the next task? I am fairly new to ActiveX in DTS.Thanks,JackIt is what it is. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-09-24 : 15:16:06
|
| The above script worked fine when I created a DTS package with one VBScript using the above command, plus one execute SQL task. If the file exists, it updates a column in a table that I have. If the file doesn't exist, nothing happens. The DTS package worked exactly as designed using your code. Which service pack are you using for SQL Server? The question is both for the server and for your client. I've got service pack 3a installed on my XP client machine and also on the server.Tara |
 |
|
|
jsiedliski
Yak Posting Veteran
61 Posts |
Posted - 2003-09-24 : 15:29:21
|
| The code works, I am just looking for an explanation.1. Not sure what this is doing:Set fso = CreateObject("Scripting.FileSystemObject")2 A And what this is doing:Main = DTSStepScriptResult_ExecuteTask2 B - are the only choices ...ExecuteTask and ...DontExecuteTask?3. BOL does not have much help on ActiveX for DTS. Do you know of other resources?4. Out of curiosity, when the file does not exist, are you getting an error on the ActiveX step or does it stop?Thanks,JackIt is what it is. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-09-24 : 15:39:34
|
quote:
Originally posted by jsiedliski
The code works, I am just looking for an explanation.1. Not sure what this is doing:Set fso = CreateObject("Scripting.FileSystemObject")
The above line is required in VBScript when dealing with files. For more information about it, check out devguru.com or some other VBScript site or book.quote:
Originally posted by jsiedliski
2 A And what this is doing:Main = DTSStepScriptResult_ExecuteTask2 B - are the only choices ...ExecuteTask and ...DontExecuteTask?
2A says execute the next task. 2B says do not execute the next task. You do this to control failures and successes.quote:
Originally posted by jsiedliski
3. BOL does not have much help on ActiveX for DTS. Do you know of other resources?
Check out sqldts.com.quote:
Originally posted by jsiedliski
4. Out of curiosity, when the file does not exist, are you getting an error on the ActiveX step or does it stop?
The package fails, stops when the file does not exist. The step that fails is the ActiveX step. It fails because you specified Main = DTSStepScriptResult_DontExecuteTask, which is what you want.Tara |
|
|
|
jsiedliski
Yak Posting Veteran
61 Posts |
Posted - 2003-09-24 : 17:42:09
|
| Greatly appreciate your help :) Also, thanks for the VBScript yesterday. You helped be get into a whole new spectrum of DTS.It is what it is. |
|
|
|
|
|
|
|
|
DTS - ActiveX