October 16, 2006 at 8:16 am
I am logged onto the server, as the server startup account, which is the same as the SQL Agent account, for all the situations below.
When I run a SQL Agent job that runs an SSIS package with a Send Mail task, the job fails with the helpful error "The step failed". The OnError log message of the SSIS package Send Mail task is "Failure sending mail." (Other steps in the package run fine.)
I can run the SSIS package interactively (on the server, logged on as that same account) from BIDS and it sends the email fine.
If I go to a cmd prompt, and run the package with a dtexec command, it fails with "Failure Sending Mail."
I can log on the server as that same account, open a query window in Mgmt Studio, and run an xp_send_dbmail command, and it sends the email.
Any clues what is going on?
Holly
October 16, 2006 at 8:26 am
Perhaps I should also add that the SQL Agent job sends the job completion notifications fine, both when Agent is set to SQLMail or when set to Database Mail. This is not a problem with job notifications.
Holly
October 16, 2006 at 9:23 am
The workaround that I am using is to use sp_send_dbmail in an Execute SQL Task in the SSIS package instead. That runs fine by a SQL Agent job. But I would really appreciate advice how to use the Send Mail task with SQL Agent.
Holly
October 17, 2006 at 11:42 am
you may have to create step in your SQL Agent JOB or point to the SP that will be defined in that STEP.for e.g. STEP 3 which will execute STORE PROCEDURE & within that SP there will be SEND MAIL code.
October 17, 2006 at 11:49 am
Well, I could try that and it will be interesting to see if it succeeds or fails, but it does not enable me to use the Send Mail Task inside the SSIS package. In other words, if my Send Mail functionality has to take place after 10 SSIS tasks and before 5 others inside the SSIS package, then in order to execute it as a stored proc from the Job Step, I would have to break the SSIS package into two packages, and make the first part Step 1 of the job, then the stored proc to do Send Mail as Step 2 of the Job, and then the rest of the SSIS package in another package as Step 3 of the job. I want to keep the Send Mail task in the sequence of the SSIS package.
Surely SQL Agent can run an SSIS package containing a Send Mail task. I must have something configured wrong, or not configured, to make it work, and that's what I would like to figure out.
Thank you so much for replying.
Holly
October 18, 2006 at 2:32 pm
I had all sorts of headaches getting SQL Agent / Database Mail to work consistently... I can't really offer help in the line of a fix, but I can certainly commiserate!
I have a custom stored procedure designed to send emails to staff by reading their email addresses from a table if data in another table that is refreshed daily changes to contain certain keywords. The actual stored procedure worked like a champ... sent emails every time I ran it manually in SSMS. Then I tried scheduling it as a job, and I had to mess around with permissions and "run as" before I could get it to work via a job. Next thing I knew, the job started failing (for other reasons), and I wasn't getting notified, even though I had set myself up as an operator to be notified on failure! I went through all sorts of gyrations with starting/stopping and reading the mail logs to determine the error. I honestly tried so many things that when it miraculously started working one day, I had no idea what actually fixed it.
Good luck!
November 1, 2006 at 11:01 am
FWIW: I thought I was having this problem as well. However, it turned out to be a slow mail server. My e-mails from SSIS were sent properly, but I didn't receive them until about 30 minutes later.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply