Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Precedence constraints on Fail and Success  in DTS package

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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

722 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