Re: SQL DTS Job Status
From: Wm. Scott Miller (Scott.Miller_at_spamkillerwvinsurance.gov)
Date: 12/22/04
- Next message: Wm. Scott Miller: "Re: Win Server 2003 Web Edition Problem"
- Previous message: Sonya: "Re: browse for file"
- In reply to: DavidM: "Re: SQL DTS Job Status"
- Next in thread: Bonj: "Re: SQL DTS Job Status"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 22 Dec 2004 14:30:31 -0500
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@spam.net> wrote in message
news:OTUOHmF6EHA.616@TK2MSFTNGP09.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 in message
> news:%23GdlR2E6EHA.2180@TK2MSFTNGP10.phx.gbl...
>> 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@spam.net> a écrit dans le message de
>> news:O8q7jpE6EHA.1260@TK2MSFTNGP12.phx.gbl...
>>> 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?
>>>
>>>
>>>
>>
>>
>
>
- Next message: Wm. Scott Miller: "Re: Win Server 2003 Web Edition Problem"
- Previous message: Sonya: "Re: browse for file"
- In reply to: DavidM: "Re: SQL DTS Job Status"
- Next in thread: Bonj: "Re: SQL DTS Job Status"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
