Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 557
  • Last Modified:

Precedence constraints on Fail and Success in DTS package

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
zilll
Asked:
zilll
  • 2
  • 2
1 Solution
 
CIC AdminCommented:
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
 
zilllAuthor Commented:
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
 
CIC AdminCommented:
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
 
zilllAuthor Commented:
Thanks again, Kent! I thought I can get away without serial, but it will be messy. I'll probably with serial.

Regards,
-Albert
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now