Send Mail Task Fails in DTS Package Called Inside a VB Program

Question

Send Mail Task Fails in DTS Package Called Inside a VB Program

Carla Cabingan

SSC Enthusiast

Points: 110
More actions
March 20, 2007 at 5:39 am

#369824

Hi! Newbie here. I've been running out of options, so I would greatly appreciate it if someone could give me some tips, some links, or just point me in the general direction.
I have this DTS package residing in an sql server 2000 db server. I call it programmatically inside a Visual Basic 6 program. It runs a stored procedure, fetches some data according to several parameters (date range, etc.), and saves the data into a comma-delimited text file. The text file is attached to an email which is sent to some people.
It works okay except for the part when it has to send an email to some people to notify them of the result of the data extraction. It saves the text file and sends the notification email when I run it on my PC. It saves the text file but doesn’t send the email when I run it on someone else's PC.
Now there are several DTS packages that have the same logic and they all fail on the Send Mail step.
Does SQL server use the email profile (connected to the Windows login) of whoever created the package to send the email? What I did was recreate the package while I was logged in as the administrator of the server. If I run it on my PC, it shouldn’t work, because it will look for the mail profile of the administrator on my PC but it won’t find it since I’m logged in as me. True enough, it didn’t.
Is is a configuration problem or what? The LoadPackageFromSQLServer sub uses Windows authentication.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Bob Robinson SSC Veteran Points: 290 More actions · Answer 1

This may be of little use to you depending on your environment, but we use BLAT. Its a command driven mail program that uses your SMTP Server. All we do in the job step is shell out and execute the appropiate BLAT command.

Carla Cabingan SSC Enthusiast Points: 110 More actions · Answer 2

Thanks. I'm trying to look at other methods to send the notification email.

Matthias Schneider-334808 SSC Rookie Points: 31 More actions · Answer 3

I use Toolsack Baseline (apparently a similar product to BLAT) all the time to programmatically send mail.

http://www.arcticlabs.com/products/baseline/

david-569361 Grasshopper Points: 20 More actions · Answer 4

HI,

I am looking to do the samething

I produce a file with DTS and i want to email it

you can share a code how to send the email with dts.

Thank You!

david Nahmias

david@sepharad.com

rlondon SSCarpal Tunnel Points: 4653 More actions · Answer 5

I haven't tried this with DTS, but I have some visual basic scripts that use CDO to send e-mails with attachments. If you think this will help, below is a link that tells you how to do it.

http://www.microsoft.com/technet/scriptcenter/resources/qanda/nov04/hey1129.mspx

Carla Cabingan SSC Enthusiast Points: 110 More actions · Answer 6

thanks for all the suggestions. however, what i did (months ago) to solve the problem is to run the xp_sendmail stored proc inside an Execute SQL task. i use an ActiveX Script task to edit some of the parameters like the recipients, subject, attachments, etc. it runs perfectly most of the time, except when the server is restarted and i have to execute xp_stopmail and xp_startmail.