September 29, 2009 at 5:21 am
[font="Verdana"]Hi All,
I have one DTS package where I have to shoot a mail whenever DTS get fails. Currently the workflow precedence between tasks are On success. So I changed the workflow precedence between tasks from On Success to On Completion so that DTS keep continue its execution till the last step/task at which I shoots mail. Upto this everything is fine.
However what I wants to do/know is whenever any task/step get fails at that point DTS should shoot mail and quit its execution. I do not want DTS to keep its execution even tough any of the task/step get fails.
Can anybody suggest on this?
Thanks in advance,
Mahesh
[/font]
MH-09-AM-8694
September 29, 2009 at 9:03 am
I'm not sure I understand your question. Do you want the package to send an email and stop package execution if a task fails?
If so, then have an "On Success" workflow and an "On Failure" workflow for each step. The "On Success" leads to the next step while the "On Failure" leads to a Send Mail task.
You can set the package to fail on error on the Logging tab of Package Properties. Set the check box for "Fail package on first error".
Greg
September 30, 2009 at 12:37 am
[font="Verdana"]
Thanks Greg for you reply. Yes, you understood right. I want my DTS package to send an email and stop its execution if any task fails. But I didn't get you:
If so, then have an "On Success" workflow and an "On Failure" workflow for each step. The "On Success" leads to the next step while the "On Failure" leads to a Send Mail task.
Also if I set the Package property to "Fail on first error" where should I keep my mail sending script as this is the last task/step in my package.
Can you please suggest me this?
Thanks in advance,
Mahesh[/font]
MH-09-AM-8694
September 30, 2009 at 10:27 am
You'll use branching workflow. The "on success" workflow leads to the next sequential step and the "on error" workflow leads to the Send Mail task. You can use the existing Send Mail task and have all the "on error" workflows lead to it.
I apologize for suggesting the "Fail Package on First Error" setting. That would make the package fail without executing the Send Mail task. If you use the "on error" workflow I suggested, the package will fail anyway after the message is sent.
Greg
October 1, 2009 at 2:12 am
[font="Verdana"]Thanks Greg for your suggestions.
Here I would like to know one thing. While setting DTS package properties, I had enabled Use Transactions option in DTS package properties - Advanced Tab. So that transaction will be rolled back whenever DTS get fails.
Transaction Isolation Level is Read Committed These both options are as default behavior. However when I run DTS, one of my step (whom I have intentionally kept to fail) fails, later my DTS keeps its execution as I had On Completion workflow precedence. When I verified the destination table, I found they had records transferred successfully.
Can you please suggest me, what the exactly use of keeping the Use Transactions option enable? Why didn’t me transactions get rolled back? Question sounds bit silly, but I am bit new in DTS programming.
Thanks in advance,
Mahesh
[/font]
MH-09-AM-8694
October 1, 2009 at 1:10 pm
Hi Mahesh,
To have a step join the package transaction: in a addition to checking Use transactions, you must also select the Join transaction if present checkbox in the workflow properties of the step (right-click and select Workflow|Workflow Properties, and choose the Options tab).
Greg
October 7, 2009 at 9:08 pm
[font="Verdana"]Hi Greg,
I had implemented the solution, attached herewith. Now the big problem is we have Service Pack 1 for Windows 2003 Server and SQL Server 2000 on all the servers. Also we will not get permission to install Service Pack 2 from client and they had suggested to implement On Success + On Failure precedence between all the steps/task.
Further to accomplish the requirement, I had implemented this client's solution, however it is not working.
Say I have 3 tasks: T1, T2 and T3 and one Error mail sending script S1. precedence between T1 and T2, T2 and T3 is on success. Now as per clients suggestion I have On Failure precedence for each step, so if T1 fails it will route to S1, so if T2 fails it will route to S1 and so on. In this case S1 is not getting executed. Whenever I maintain only one On Failure precedence for S1 out of these three task it works.
So I am bit confused, if DTS allows to add such precedence then what would be the problem for not executing? Can you please suggest me on this? And also let me know if you need more information on it
Thanks in advance,
Mahesh[/font]
MH-09-AM-8694
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply