Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Precedence constraints on Fail and Success  in DTS package

Posted on 2006-11-17
4
Medium Priority
?
551 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 1000 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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

773 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