October 22, 2007 at 7:35 am
I want to generate a log file to confirm successful execution of DTS package and send email notification on successful execution. Can anybody help me how it can be done. Do I need to develop a script or there is already ways to do it in SQL.
October 22, 2007 at 8:00 am
If all you want to know is that it competed successfully, you can explore the optiosn from the SQL Agent job, notification tab and have it send a mail on both success and failure.
Or you could set the last step of your package to send you a mail you'd just need to set a few On Success contraints on your package workflow.
If you really want detailed logging, from design view check out the Package|properties|Logging Tab, and log it to your server somewhere and then again the last step of the on the job have it query that package and mail you the results.
October 22, 2007 at 8:40 am
Thanks Luke
Package|properties|Logging solves my problem. But please explain how can I do the last step of mailing the results.
Also I want to make sure that whenever the database is backed up, this log file containing the DTS execution information is also backed up alongwith the database.
October 22, 2007 at 4:21 pm
If you are logging the execution to a table in the database, then that table will be backed up along with the rest of your database. Also the execution history (if you right click on the SA job and choose view history) is stored in the Master Database.
As for mailing the results, you'll need to setup SQL mail, you can search this site and books online for how to configure it.
Then just use the DTS Send Mail task to send an email with the results from querying the logging table in either another step or another package all together.
-Luke.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply