I have a DTS Job that is scheduled to run daily on our SQL Server 2000
server. Is there an easy way for me to determine if the DTS job ran
successfully?

Does DTS provide anything for this or must I use DMO to look at the jobs
collection?

Hi David!

For you as a programmer or for you as a user ?
Is the job schedule directly on the server or is it called from a VB Program
?
if so, do you wish to receive the return value in your VB Program ?
if so ,do you it to be a sync or async DTS call ?

here's a fast thought :
if the job is schedule only on the server i think you could use xp_sendmail
to send an email at the end of the DTS execution or maybe you could try
xp_logevent which logs an event in the Microsoft SQL Server log file and in
the Microsoft Windows NT Event Viewer..

--
Best Regards
Yanick

"DavidM" <spam (AT) spam (DOT) net> a écrit dans le message de
news:O8q7jpE6EHA.1260 (AT) TK2MSFTNGP12 (DOT) phx.gbl...

Actually -- the DTS package runs on server and all is fine. However, I have
a VB application that loads/reports information to the user based on some of
the data that was loaded from DTS.

I thought it would be nice to have my VB application display the last time
the DTS package was run and whether it was successful - just as an
informational type thing.

Today, we usually don't know if the DTS package fails until we run the
reports and then have to go and see why data is not accurate.




"Zoury" <yanick_lefebvre at hotmail dot com> wrote

David:

Well since you are already generating reports, just create another table in
your DB and have the DTS task run an Execute SQL task to write a record to
the table using CURRENT_TIMESTAMP. That will record everytime the task
runs. Additionally, you can delete all records out of the table if you want
or leave them for historical purposes.

Another option is to set up an Operator in SQL Server and have the SQL Agent
e-mail you if it fails or succeeds. This method requires MAPI to be
installed on the SQL Server box to work (e.g. Outlook must be installed).
Additionally, this can be problematic at times on a chatty network or if the
e-mail server (exchange) goes down. If you lose communication to the e-mail
server, you will have to stop and restart the SQL Agent task from EM to get
it back online.

Lastly, you could also use the Send Mail task to send an e-mail to you if
tasks fail. You have to use the "On Failure" workflow. Beaware that all
lines must enter a task in order for it to run. So if an error can occur in
multiple places you will have one Send Mail task for each failure. Can
become quite busy. Also, make sure you give the MAPI connection the same
name on your workstation as on the server or you can shoot yourself in the
foot really easily. Again, requires MAPI.

There are other methods of sending mail (see previous post by Zoury for one
such method).

Scott

"DavidM" <spam (AT) spam (DOT) net> wrote

The DTS.Package object has a WriteStatusToNTEventLog property.
This forces the DTS to write its completion status to the event log of the
machine on which it is executed (note that if you call a DTS which resides
on server A from a process running on client B, then it would write to the
client's event log, not the server's). You can then easily check in there
manually to make sure it's doing it, from then on - I don't personally know
how to read the event log from VB, but I would imagine it's easily possible.



"DavidM" <spam (AT) spam (DOT) net> wrote