Solved

Precedence constraints on Fail and Success  in DTS package

Posted on 2006-11-17
4
533 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

630 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