 | |
DTS package Precedence not working
microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
DTS package Precedence not working -
10-21-2004
, 08:39 PM
#2
| |||
| |||
|
|
I have a master DTS package that is made up of multiple other DTS packages. The process that we run nightly is something like: Step 1 (pull data) On Success Step 2 (aggregate data) On Success Step 3 (generate key in separate table) On Success Step 4 (push data into final table) It's important that the package come to a hault if something goes wrong in step 3 as we don't want records in the table (step 4) that don't have corresponding records in the key table. Hence we put a workflow step in between each execute dts package task saying move to the next step "on success". However in observing the package execute it seems to move the next step regardless of the results in the prior step. If step 3 fails step 4 starts going instead of aborting the master package? Wondering if this is a known issue? Any help would be greatly appreicated. I"m using SQL SERVER2k on a Win2k3 server. Thanks in advance. Josh |
#3
| |||
| |||
|
|
I have a master DTS package that is made up of multiple other DTS packages. The process that we run nightly is something like: Step 1 (pull data) On Success Step 2 (aggregate data) On Success Step 3 (generate key in separate table) On Success Step 4 (push data into final table) It's important that the package come to a hault if something goes wrong in step 3 as we don't want records in the table (step 4) that don't have corresponding records in the key table. Hence we put a workflow step in between each execute dts package task saying move to the next step "on success". However in observing the package execute it seems to move the next step regardless of the results in the prior step. If step 3 fails step 4 starts going instead of aborting the master package? Wondering if this is a known issue? Any help would be greatly appreicated. I"m using SQL SERVER2k on a Win2k3 server. Thanks in advance. Josh |
#4
| |||
| |||
|
|
I noticed a check-box, in the Logging tab of the Package Properties, called "Fail package on first error". Will ticking this box generate the desired behaviour? I will probably try to test this. "TCG_GILBERT" wrote: I have a master DTS package that is made up of multiple other DTS packages. The process that we run nightly is something like: Step 1 (pull data) On Success Step 2 (aggregate data) On Success Step 3 (generate key in separate table) On Success Step 4 (push data into final table) It's important that the package come to a hault if something goes wrong in step 3 as we don't want records in the table (step 4) that don't have corresponding records in the key table. Hence we put a workflow step in between each execute dts package task saying move to the next step "on success". However in observing the package execute it seems to move the next step regardless of the results in the prior step. If step 3 fails step 4 starts going instead of aborting the master package? Wondering if this is a known issue? Any help would be greatly appreicated. I"m using SQL SERVER2k on a Win2k3 server. Thanks in advance. Josh |
#5
| |||
| |||
|
|
I believe this check box is to fail on error of writing the log. Not on error of the dts package itself. |
#6
| |||
| |||
|
|
I checked through a bunch of other posts here, and found a couple which also described this problem. Both were answered by a Darren Green who indicated that this check box will work. Have a look at the post dated 10/21/2004 entitled "Execute Package Task - Workflow". I will definitely test it now. "tcg_gilbert" wrote: I believe this check box is to fail on error of writing the log. Not on error of the dts package itself. |
#7
| |||
| |||
|
|
The fail on first error will mean the child stops on error, no on error workflow will be followed, but more importantly for you this also means the error is bubbled up to the Exec Pkg Task in the parent and this task also then fails. -- Darren Green http://www.sqldts.com "Les Russell" <LesRussell (AT) discussions (DOT) microsoft.com> wrote in message news:B70D8547-838C-4A42-8C17-68733A2E6BCA (AT) microsoft (DOT) com... I checked through a bunch of other posts here, and found a couple which also described this problem. Both were answered by a Darren Green who indicated that this check box will work. Have a look at the post dated 10/21/2004 entitled "Execute Package Task - Workflow". I will definitely test it now. "tcg_gilbert" wrote: I believe this check box is to fail on error of writing the log. Not on error of the dts package itself. |
#8
| |||
| |||
|
|
I have a master DTS package that is made up of multiple other DTS packages. The process that we run nightly is something like: Step 1 (pull data) On Success Step 2 (aggregate data) On Success Step 3 (generate key in separate table) On Success Step 4 (push data into final table) It's important that the package come to a hault if something goes wrong in step 3 as we don't want records in the table (step 4) that don't have corresponding records in the key table. Hence we put a workflow step in between each execute dts package task saying move to the next step "on success". However in observing the package execute it seems to move the next step regardless of the results in the prior step. If step 3 fails step 4 starts going instead of aborting the master package? Wondering if this is a known issue? Any help would be greatly appreicated. I"m using SQL SERVER2k on a Win2k3 server. Thanks in advance. Josh |
#9
| |||
| |||
|
|
Thanks for this Darren. I guess that means that you cannot easily have a child process which both has on-error processing, but which you also want to signal a fail to a parent if any other tasks fail. Any clues as to how this could be done - ActiveX would be required I guess? "Darren Green" wrote: The fail on first error will mean the child stops on error, no on error workflow will be followed, but more importantly for you this also means the error is bubbled up to the Exec Pkg Task in the parent and this task also then fails. -- Darren Green http://www.sqldts.com "Les Russell" <LesRussell (AT) discussions (DOT) microsoft.com> wrote in message news:B70D8547-838C-4A42-8C17-68733A2E6BCA (AT) microsoft (DOT) com... I checked through a bunch of other posts here, and found a couple which also described this problem. Both were answered by a Darren Green who indicated that this check box will work. Have a look at the post dated 10/21/2004 entitled "Execute Package Task - Workflow". I will definitely test it now. "tcg_gilbert" wrote: I believe this check box is to fail on error of writing the log. Not on error of the dts package itself. |
#10
| |||
| |||
|
|
Exactly. Bit of a trade off when using the Exec Pkg Task. The alternative is to use another execution mechanism, such as doing it directly from ActiveX Script. -- Darren Green http://www.sqldts.com "Les Russell" <LesRussell (AT) discussions (DOT) microsoft.com> wrote in message news  E8767D0-C4DB-4262-BADD-3FFAB8B48629 (AT) microsoft (DOT) com...Thanks for this Darren. I guess that means that you cannot easily have a child process which both has on-error processing, but which you also want to signal a fail to a parent if any other tasks fail. Any clues as to how this could be done - ActiveX would be required I guess? |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.