DTS Multi Way Logic

Question

DTS Multi Way Logic

deucellion

Say Hey Kid

Points: 680
More actions
September 4, 2007 at 9:45 am

#70200

Hi All,
The company I am working for has a number of DTS packages that process and import files in a similar manner. The first step is always an ActiveX script to check that a file exists:
IF (fso.FileExists(DTSGlobalVariables("gPath").Value)) = "True" THEN
Main = DTSTaskExecResult_Success
ELSE
Main = DTSTaskExecResult_Failure
END IF
So if the file exists it continues on to the next step in the work flow, otherwise it will take the Failure route which sends an email then exit setting the job status to failure.
This is the correct processing but we don't want to set the job status to failure (the file won't always exist), we simply want to send an email and exit the package setting the job status to completed.
Does anyone know of any strategies for representing this multi success logic?
Many Thanks

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

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

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

I believe there's an article about how to do this at http://www.sqldts.com, but their site is inaccessible right now so I can't verify that.

I have a similar requirement in a scheduled import job, but I found it easier to do the "file exists" check in a job step. If the file exists, the job goes to the next step, which executes the DTS package. If the file doesn't exist, it goes to another step which sends a notification email and quits the job with success.

Greg

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

What you're asking to do is built right into DTS - just make sure you use the "on success" and "on Failure" connectors. Assuming you put the "e-mail customer on no file" in a separate step, and you connect the first step to this one based on failure, and the "main" execution executes "on success" your logic should work as is.

The job will report success in either case, assuming the very last step in either branch is reached and executes correctly. Of course - if the e-mail steps ALSO fails, then the job fails.

On complete connectors fire whether success or failure.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

deucellion Say Hey Kid Points: 680 More actions · Answer 3

Thanks Greg. Good idea re. creating an extra step.

I've managed to get it to work in a package by creating 2 success workflows out of the "file exist" step and then disabling the appropiate workflow depending if the file exist or not:

IF (fso.FileExists(DTSGlobalVariables("gPath").Value)) = "True" THEN

' Ensure that the next step is enabled

pkg.Steps("DTSStep_DTSActiveScriptTask_3").DisableStep = FALSE

pkg.Steps("DTSStep_DTSActiveScriptTask_2").DisableStep = TRUE

ELSE

pkg.Steps("DTSStep_DTSActiveScriptTask_3").DisableStep = TRUE

pkg.Steps("DTSStep_DTSActiveScriptTask_2").DisableStep = FLASE

END IF

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 4

I'd have to agree with Matt on this one. DTS has built-in workflows, so why not use them:

On Success - The next task executes when the previous task completes successfully

On Failure - The next task executes when the previous task reports a failure

On Completion - The next task executes when the previous task completes - reporting success or failure

To change an already existing workflow path simply right-click the path and choose Properties. In the properties dialogue change the Precedence from the drop-down menu.

Using ActiveX like this is best left for making alternate decision paths based on variable results.

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

Chad Carter Ten Centuries Points: 1089 More actions · Answer 5

This can be done using the Options tab of the WorkFlow properties. To see this tab you actually select the 2 tasks the workflow is going between and then Right Click > WorkFlow > WorkFlow properties. On the Options tab there is a ActiveX area that you can use.

So instead of setting a success / failure in your first ActiveX you can set a global variable (ie. 0 or 1). Then is the ActiveX for the WorkFlow propoerties you can evaluate this global variable and use the Execute or DontExecute settings.

If you file doesn't exist it will complete the DTS package after running the check step. If it does exist then it will continue through the rest of the DTS package. And the plus side to this is it doesn't report a non-existent file as a failure.

Hope this help!

Chad