2005 SSIS Send Mail using MAPI

[grnzbra]

I am trying to create an SSIS package that will loop through a table and send an e-mail to each person on the list. I have created a working package at home that uses SMTP, but at work, it has to use MAPI. I have not been able to find any info about how to do this; only info, blogs, and posts that tell me not to - to use SMTP. How do I set up an SSIS package in SQL Server 2005 to send e-mails using MAPI?

 

[mrdenny]

SSIS doesn't use MAPI, it uses SMTP. Sending email via MAPI is considered legacy as SMTP is used starting in SQL 2005.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[grnzbra]

Unfortunately, what we have is MAPI. We are trying to get them to set up SMTP, but if they don't, MAPI is what I'm stuck with.

 

[mrdenny]

If you have Exchange setup then SMTP should be available on the exchange servers or the mail gateways.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[grnzbra]

Yes. I understand that Exchange is what we have, but the techies have disabled it or not turned it on and their attitude is "You'll work with what we give you". In any event, what we have to work with is MAPI. If we can't talk them into enabling SMTP or get SendMail working with MAPI, a whole bunch of people are suddenly not going to get their reports in a timely manner.

 

[mrdenny]

As far as I know SSIS doesn't support MAPI at all. Create a business case for needing SMTP and present it to your manager. They can then decide if the reports need to go out, or not. If they do then they can have the Exchange admin enable SMTP.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[grnzbra]

Does SMTP Services (under Add/Remove Windows Components in Add or Remove Software on Control Panel) have to be turned on for Send Mail to work. Will it work without it. I turned it on on my home machine because that is what the tutorial instructed. I then set the smtp server to smtp.east.cox.net (ie a real e-mail server, not a test setup for a home network). I assumed that the SMTP Services was needed.

At work I connect to a machine with SQL Server 2005 by going through a Citrix connection and then to a Remote Desktop to a machine "out there" somewhere. We have asked for an SMTP mail server and for SMTP Service to be turned on on the machine that has SQL Server. They asked why and I replied that the SMTP Connection Manager was looking for the SMTP Service to be turned on.

The response to the request for SMTP Service was,
"It’s looking for “an” SMTP server, not necessarily one on localhost. If you put a hostname of a remote smtp server into the configuration wizard, it will work fine. This is the direction you should go. Security is going to shoot your app down or you will need all kinds of waivers and such to run an SMTP server. The enterprise already runs this as a service and should be used in all cases where possible."

Is this correct?

They gave me a hostname and it still doesn't work. The only error messages I get when I run the package are:

"An error occurred with the following error message 'Failure sending mail'"
and
"Task Send Mail failed"

Is there anywhere that I can find a more detailed explanation of why it failed (ie Send Mail set up wrong or SMTP Connection manager set up wrong, etc).

 

[mrdenny]

No you don't need the SMTP service installed locally. Telnet to port 25 on the SMTP server that they gave you. If you can connect then it should work, if not then there's a firewall config error that needs to be corrected.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog