Send Email If any Package step fails

[MrPeds]

Hi,

This is probably another easy one but:

I have currently got my DTS Packages to automatically fail upon the first error encountered in the package, e.g. Max erro r count is zero in the Package Properties.

How can i set my Packages up to automatically send an email to me if a DTS package fails at any step in the package? The kind of information I would like to send is: package name, and error message and that is about it.

Do I need to set this as a package property somewhere or write some custom code?

Thanks,

MrPeds

 

[nigelrivett]

Usually you would do this with a loader.
Write something that loads the package then on error goes though each step retrieving errors and other info and emailing it. You can also put other functionality in the loader like saving the start/ed time, setting servers/databases...

If you just want an error then send it from whatever loads the package (the agent).

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[MrPeds]

nigelrivett ,

When you say loader, do you mean something like DTSRun, or the SQL Scheduler?

How would i go about setting this up? are there any examples on the web or...?

Thanks,

MrPeds

 

[nigelrivett]

No - I mean an app you write to load the dts package.

It can be in t-sql, VB, a dts activex script, or anything that will deal with com.

The simplest thing might for you to turn logging on in the package and get the agent to email you the log file on an error. You could look inside the log file to get the error message but I think you would be better off looking at the steps in the package if you want to do that.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.