Active-X DTS Problem

Question

Post reply

Active-X DTS Problem

Andrew Liles

Mr or Mrs. 500

Points: 520
More actions
May 20, 2004 at 1:51 pm

#161582

Hello All
I am using Active-X to write a DTS package. On a successful completation of an Active-X script that uses and IF THEN ElSE statement. How can I determine if there was a success? And then How do I cal the next process in line.
Example...
IF Variable A <> Variable B THEN
go to SQL Task A -- to Truncate an Exising Table and then just End the DTS package
ELSE
go to SQL Task B -- To Load an Existing Table and then just end to DTS package
END IF
------------------------------------------------------
Thanks in advance for you advise and assistance
Andrew

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47795 More actions · Answer 1

Why are you using ActiveXScript? Why not use the inbuilt workflow?

--------------------
Colt 45 - the original point and click interface

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 2

If what you want to do is set up a variable workflow where your ActiveX script determines if you execute one leg of the workflow vs. the other then you've got some work ahead of you. It's not one of the simplest concepts, but then once you get it working in one package you'll use it a lot.

Try http://www.sqldts.com. They have at least one example of a VBScript ActiveX variable workflow: running a loop within your DTS package. The code at the end of the loop determines if you're through processing the list in a Global Variable, if not it sends you back to the beginning, if so then it continues to the next step. You can apply the same principles to your problem.

If you get stuck then email me and I'll send you a DTS package with a variable workflow in it that you can cannibalize.

Tom Hamilton-126456 Mr or Mrs. 500 Points: 527 More actions · Answer 3

I evaluate my control and set a variable that is used to enable or disable the 'next' DTS step. This particular example is used for looping a set of files, please feel free to adapt as needed. This code was adapted from the SQLDTS.com site and I thank them.

Hope this helps

Option Explicit

Function Main()

Dim pkg

Dim conTextFile

Dim stpEnterLoop

Dim stpFinished

' We want to continue with the loop only if there is one or more text files in the directory.

'If ShouldILoop = True then we disable the step that takes us out of the package and continue processing

SET pkg = DTSGlobalVariables.Parent

SET stpEnterLoop = pkg.Steps("DTSStep_DTSActiveScriptTask_1")

SET stpFinished = pkg.Steps("DTSStep_DTSExecuteSQLTask_3")

if LoopOK = True then

DTSGlobalVariables("gv_Reps").Value = DTSGlobalVariables("gv_Reps").Value + 1

stpEnterLoop.DisableStep = False

stpFinished.DisableStep = True

stpEnterLoop.ExecutionStatus = DTSStepExecStat_Waiting

else

stpEnterLoop.DisableStep =True

stpFinished.DisableStep = False

stpFinished.ExecutionStatus = DTSStepExecStat_Waiting

End if

Main = DTSTaskExecResult_Success

End Function

Function LoopOK

Dim fso

Dim fold

Dim counter

Counter = 0

SET fso = CreateObject("Scripting.FileSystemObject")

SET fold = fso.GetFolder(DTSGlobalVariables("gv_DirUpld").Value )

Counter = fold.files.count

If Counter >= 6 then

LoopOK = CBool(True)

Else

LoopOK = CBool(False)

End if

End Function

Andrew Liles Mr or Mrs. 500 Points: 520 More actions · Answer 4

Thank you everyone. I checked out http://www.sqldts.com and that did the trick. Thanks again for the great advise on DTS. Please feel free to email me if you have any SQL DB problems or questions.

Andrew