October 23, 2009 at 4:04 am
Not SSIS, but DTS so the knowledge should be in here hopefully.
I asked this a while back but didn't get much help but the issue has come up again...
I have a DTS Package in SQL 2K.
Step 1- Execute SQL Task. The package needs to look up a value in a table. If the value is > 0 it gets a package variable to "GO".
Step 2-ActiveX Script Task - If Variable = GO then Task is set to Success else set to Fail.
Step 3a - If success workflow then run all of the 10 following steps of scripts and t-sql.
Step3b - If failure then exit the package.
The trouble is that if you set the workflow to failed, the package will report as "Failed" even though I handled the failure path.
I want the job to report as successful.
The question is - How do I force a package to complete as successful in DTS?
I have tried using Dynamic Properties and that is no use to me here. I have toyed with the idea of putting a check for that variable value in every script and t-sql step but that is messy.
Thanks!
October 23, 2009 at 7:18 am
Add an Active X Script task to the end of your Failure flow that simply sets the execution result to Success.
October 23, 2009 at 7:50 am
That was my plan, so I now need to find out how to set the package execution to success. I can set the step to be successful, but ultimately the job fails because there was a failure on one of the previous steps.
October 23, 2009 at 8:12 am
😀 SOLVED IT AT LAST.
Step 1 checks for a value. If the value is NOT met it sets a Global Variable @DISABLE = 1 else @DISABLE = 0.
Step 2 is now a Dynamic properties task. It sets the Disable property of each of the remaining steps to the value of Global Variable @DISABLE.
Step 3 and the steps following will now either run (if value was met) or will not run as they are set to disabled.
GET IN!
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply