Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
william_lee78
Yak Posting Veteran
53 Posts |
 Posted - 2005-10-05 : 06:03:53
|
| Hi All,Is anyone has done this before?I have a DTS package that transfer some records to one Excel file and one Access file on a remote machine. It is scheduled to run on every Sunday midnight. I would like to have some kind of notification mechanism or tracer to inform me the status of the execution (whether success or fail)? To be more specific, I would like to know whether the Excel file and Access file are generated successfully or is there any error happen during the execution of the DTS package. For failure case, I would like to capture the step when the error occur, the details of the error. Is it possible to achieve this type of operation?It will be must appreciate if someone can teach me how to do this. Thank you in advance for the help. |
|
|
william_lee78
Yak Posting Veteran
53 Posts |
Posted - 2005-10-09 : 23:22:02
|
| Hi All,Since no reply, is it mean this requirement is impossible to achieve?Regards,William |
 |
|
|
rav3n0u5
Yak Posting Veteran
88 Posts |
Posted - 2005-10-11 : 18:37:00
|
| I don't know much about this stuff - but I will share what I think I know, and it might help..In your DTS package, you can include events on failure and on success (see Workflow). If you have email setup, then you can create 2 email procedures - one to email on success, one to email on failure. Alternatively, you can look at the Windows Event Log, which should be logging this stuff (Start-->run -->eventvwr). I dont know how to get the level of granularity you are looking for, but I think the above is a good start. |
|
|
|
william_lee78
Yak Posting Veteran
53 Posts |
Posted - 2005-10-11 : 22:38:39
|
| Hi,Thank you for the reply. Is it the only way I can do this? In my DTS package, I have lots of tasks (around 13 tasks). If I've to use the onFailure and onSuccess workflow, then I have to create lots of branches for the tasks that I have, am I right? Is it possible to have an additional step that is able to check for the status of the 13 tasks then send the email out to the operator? Anyway, thank you so much for the reply. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-10-12 : 03:37:08
|
| if your tasks involve sql queries or calling SPs, you can incorporate error handling thereHTH--------------------keeping it simple... |
|
|
|
william_lee78
Yak Posting Veteran
53 Posts |
Posted - 2005-10-12 : 23:11:55
|
| Hi Jen,Thank you for the reply.Yes, my DTS package contains some sql queries in the Transform Data task. How can I incorporate error handling there? Can you provide with some sample?? Thank you. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-10-13 : 00:40:26
|
you can use begin tran and commit tran or rollback tranby catching the error like..your queryif @@error<>0 rollback tran --or use goto so you can skip the next line of codes and exit with a failed taskelse proceed with otherif you post a part of your task, we can probably give you a clearer sample if this is still vague... --------------------keeping it simple... |
|
|
|
|
|
|
|
|
Notify DTS Package Execution Status [?]