when is 'success' workflow really a failure ??

Question

when is 'success' workflow really a failure ??

rwall3

SSC Enthusiast

Points: 139
More actions
October 13, 2003 at 8:53 am

#106636

Hi Guys
I have a hierarchical set up of DTS packages from child to parent to grandparent, using the 'execute package task' to build up the hierarchy, so that my DTSRun schedule only has to call 1 package and the order of my 50+ packages is handled via the hierarchy. The problem is that the 'success' workflow doesn't seem to work when joining one 'execute package' to another 'execute package'. Well, it's not that it doesn't seem to work - it blatantly does not work. If one package fails and it's successor is joined via success workflow, the successor is executed!! Has anyone come across this before and found a resolution?
My Regards
Ryan Wall

Viewing 8 posts - 1 through 7 (of 7 total)

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

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

Haven't had this problem before. The workflow worked for me when I setup 140+ packages with 4 levels.

Are you sure you're failing the child package? We had an issue when an error was correctly handled and logged in the child package. It then passed a success status back to the parent. The success status actually indicated successful logging of the error message. We had to change some logic in an ActiveXScript task that logged the errors.

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

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

rwall3 SSC Enthusiast Points: 139 More actions · Answer 2

Hi Phill

We aren't explicitly handling 'failing' a child package. I assumed that if a step failed within the child, and therefore the child package itself failed, then it would return a failed status to the calling parent package. Is this not how it works?

Rgds

Ryan

johnsonj SSCrazy Points: 2159 More actions · Answer 3

I assume you have the lastest service pack, but you might want to check? I believe there was a problem something like this prior to SP3. I don't remember the details -- sorry.

Joe Johnson

NETDIO,LLC.

Joe Johnson
NETDIO,LLC.

Robert W Marda SSChampion Points: 13413 More actions · Answer 4

I have encountered the same problem. To get around it. I execute child DTS packages with an ActiveX Script task. The child package uses a global variable that gets set to success upon successful completion of everything in the child package. The VBScript in the parent child checks the value of that global variable and if it is not success then it completes the ActiveX Script with failure which is then handled in the parent DTS package.

I'll not claim this is the best way as I have not tried any other way. I do know that it works for me and the scripts are not very complex. If this sounds like something you want to investigate I can post the VBScripts I use and give more details about the process.

Robert W. Marda

SQL Programmer

bigdough.com

The world’s leading capital markets contact database and software platform.

Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems

hoosierpride Mr or Mrs. 500 Points: 565 More actions · Answer 5

In the workflow properties of the steps you should set the "Fail Package on Step Failure" for all steps you want to fail the package if they error. I use a 3-tier ETL framework and this is the way we pass errors up the tiers to cause our "Master" package to fail.

rwall3 SSC Enthusiast Points: 139 More actions · Answer 6

Hi guys - thanks for all your input, checking 'fail package on step failure' seems to be the logical answer to this - I'll give it a try.

rw

Larry Stein Default port Points: 1447 More actions · Answer 7

FYI - we are experimenting with 64-bit SQL and found that nested package error reporting behavior changed slightly. We are investigating to see whether this is a difference in 32 vs 64-bit code line or our configuration. The packages had not changed.

Larry