sql server dts: ActiveX query - continue or stop process -- sql server dts

ActiveX query - continue or stop process

Ben Rum
3/30/2005 4:50:35 PM

sql server dts:
In the below ActiveX script (taken initially from BOL) I compare file dates
& only want to continue the DTS if if the file date is newer.

However, if the file date is NOT newer the Step is "failing", whereas I just
want to STOP

The DTS is as follows (in my best plain text graphic designing):

ActiveX script
(code below) ---- on success ----> TextFileConnection ------- data
transform -----> SQLconnection
|
|----- on failure ----> (do nothing, but don't FAIL, just STOP)

Function Main()
Dim fso, file1
Set fso = CreateObject("Scripting.FileSystemObject")
Set file1 = fso.GetFile("c:\file1.txt")
Set file2 = fso.GetFile("c:\downloaded\file1.txt")
IF (file1.datelastmodified > file2.datelastmodified) THEN
fso.CopyFile file1, file2
' Clean Up
Set fso= Nothing
Main =DTSStepScriptResult_ExecuteTask
ELSE
Main=DTSStepScriptResult_DontExecuteTask '(I am getting
a FAIL here, just want to discontinue)
END IF
End Function

Many Thanks

Re: ActiveX query - continue or stop process

Allan Mitchell
3/30/2005 9:39:10 PM

You get an actual failure?

I get a "Stop" MsgBox and a black cross on my run results indicating the
task did not execute using the following in the Workflow ActiveX

Function Main()
Dim fso, file1
Set fso = CreateObject("Scripting.FileSystemObject")
Set file1 = fso.GetFile("c:\Text Files\New Text Document.txt")
Set file2 = fso.GetFile("c:\Text Files\COMLOG.txt")
IF (file1.datelastmodified < file2.datelastmodified) THEN
Msgbox "Continue"
' Clean Up
Set fso= Nothing
Main =DTSStepScriptResult_ExecuteTask
ELSE
Msgbox "Stop"
Main=DTSStepScriptResult_DontExecuteTask
END IF
End Function

[quoted text, click to view]

Re: ActiveX query - continue or stop process

Ben Rum
3/31/2005 1:53:07 PM

Adding to the previous post, I found this on google groups (posted in
microsoft.public.sqlserver.server on 25 Jul 2000, by Darren Green)

Judging by the below post, I am executing a
"DTSStepScriptResult_DontExecuteTask" whereas the return code is the same
as, and is actually running a "DTSTaskExecResult_Failure", thus resulting in
the failure...?

----------- Post from 25-jul-2000 --------------
Andrew, the constants you are using are the DTSStepScriptResult which
strictly speaking are for ActX Workflow scripts, as oppose to the
DTSStepExecResult which relate to the step's ExecutionResult property.
This holds the Task's result, hence it can only be success or fail.

DTSStepScriptResult constants
0 DTSStepScriptResult_ExecuteTask
1 DTSStepScriptResult_DontExecuteTask
2 DTSStepScriptResult_RetryLater

DTSStepExecResult constants
0 DTSTaskExecResult_Success
1 DTSTaskExecResult_Failure

My first though is that this wouldn't work, but as the constant values
are the same it doesn't care. Sorry for being pedantic, but it's bound
to cause some confusion.

----------- End Post from 25-jul-2000 --------------

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server dts group.