Solved

Precedence constraints on Fail and Success  in DTS package

Posted on 2006-11-17
4
522 Views
Last Modified: 2012-05-05
I've create a DTS package in SQL Server 2000 Enterprise Ed. that by querying 2 tables export them to Excell spreadhseet and emails as an attachment to certian people. Here are the steps: 1) drop both sheets in Excell Workbook; 2) re-create them again;  3) export results to both of these sheets; 3) send email with attachment.
The question is how can send myself Failure notification when any of the steps fail? And how can send an attachment after all steps completed successfully?

Thanks,
Albert.
0
Comment
Question by:zilll
  • 2
  • 2
4 Comments
 
LVL 5

Expert Comment

by:CIC Admin
ID: 17966812
Connect each step of your DTS package with "On Success" rather than "On Completion".   Add a new task, "Send Mail Task".  Connect to that using "On Failure".  That way, if the task completes successfully it will continue on, if it fails, it will be directed to the Send Mail Task and you will get an email that it has failed.  The final step along the success chain can be a different Send Email Task that you can add the attachment to.

To create these conditional paths, create the tasks first, then click on the first task, hold down the control key and click on the second taks.  With the two tasks highlighted, go to the workflow menu and select either "On Completion", "On Success", or "On Failure".  A dashed blue line represents "On Completion".  A dashed green line represents "On Success".  A dashed red line represents "On Failure".

Good Luck,
Kent
0
 

Author Comment

by:zilll
ID: 17967281
Thank you so much Kent. I never thought about it,
but I should mention that all these taks are going in parallel:
                 on success                     on success
drop Sheet1------------->create Sheet1----------->export records to Sheet1------|
                                                                                                                    |
                                                                                                                    Excel Spreadheet
                                                                                                                    |
                 on success                     on success                                              |
drop Sheet2------------->create Sheet2----------->export records to Sheet2-------|

Your solution to add Failure Task and point all steps to Failure Task will work, but what about the final step? Basically, as you see there 2 final steps: export records to Sheet1 and export records to Sheet2. When I tried to setup success constraint how can I tell to which one I have point to?

Thanks,
Albert.
0
 
LVL 5

Accepted Solution

by:
CIC Admin earned 250 total points
ID: 17968451
You can "funnel" two success constraints into the final step.  That way, the final step will be run ONLY if both path are successful, like so :

                 on success                     on success                                        on success
drop Sheet1------------->create Sheet1----------->export records to Sheet1----------------|
                                                                                                                               |
                                                                                                                               Excel Spreadheet
                                                                                                                               |
                 on success                     on success                                        on success   |
drop Sheet2------------->create Sheet2----------->export records to Sheet2----------------|

That way, say the sheet2 succeeds all the way but sheet1 fails to drop Sheet1 (first task), the final Excel Spreadsheet step will not be run because both sides did not succeed.

Another alternative, if timing is not an issue, is to just run them all as a series rather than in parallel :

drop Sheet1--->create Sheet1--->export to Sheet1--->drop Sheet2--->create Sheet2-->export to Sheet2--->Excel Spreadsheet
0
 

Author Comment

by:zilll
ID: 17968486
Thanks again, Kent! I thought I can get away without serial, but it will be messy. I'll probably with serial.

Regards,
-Albert
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question