SQL Server 2005 DTS Packages using SMTP

Hello,

I have a Windows 2003 Enterprise SP2 32BIT with SQL 2005 installed.  We have a bunch of legacy DTS packages.  We recently converted from Exchange to google mail.  I have spent hours trying to figure out how to get the DTS packages to use SMTP instead of MAPI.

Does anyone know how to configure a DTS package to use SMTP mail instead of MAPI?

Jeff Pierce
jpierce26Asked:
Who is Participating?
 
Jared_SConnect With a Mentor Commented:
Hi Jeff - sorry for the delay.

I don't have experience with anything prior to VS 2005, but it sounds like the packages might need migrated.

If they were SSIS packages, I believe you'd see a connection manager panel at the bottom of the solution when you open it in VS 2005 - or you would be able to go to SSIS -> Connections on the menu and see the SMTP connection.

Is it possible that the packages you're working with need migrated?
Here are a couple of sites that might be useful if a migration is needed.

http://www.mssqltips.com/sqlservertip/1224/upgrade-sql-server-dts-packages-to-integration-services-packages/

http://msdn.microsoft.com/en-us/library/ms143496(v=sql.90).aspx
0
 
Jared_SCommented:
I don't use MAPI, but you can set up SMTP like this:

Use the database mail configuration wizard in SSMS to set up email for the sql server, and then add the SMTP connection manager to your project and configure it with the name of your smtp server.

Then you can add a send mail task to your control flow and your ready to go.
0
 
jpierce26Author Commented:
I have configured the database mail and that works fine, but when I go into my DTS packages and try to use an "email task", it says MAPI isn't installed.  I had just uninstalled Outlook 2007 while troubleshooting because we used to use Exchange.

The dbmail works fine, it's the DTS packages I can't get to use SMTP mail.  Under  Management\Legacy\Data Transformation Services  there are several DTS packages that use the "send mail Task"  but I receive an error as stated above.

Under the SQL Server Agent properties, there is an "alert system" and I select the mail system to be "database mail" and use the correct profile from the DBmail settings.  But that doesn't seem to affect the legacy DTS package email task.

Does that make sense?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Jared_SCommented:
I would wager that the DTS packages need updated. Are there emails being generated within script or is it a send mail task?

Have you watched the execution of the package in VS 2005 or are you just running the job?
0
 
jpierce26Author Commented:
The emails are being created via the "send mail" task.

The only way I can get the send mail task to work is to configure Outlook 2007 client on the computer.  But because Outlook isn't configured for mapi, it errors out with a mapi error.

Jeff
0
 
Jared_SCommented:
As long as you have the right SMTP server listed in your package SMTP package connection, this should be working.

In SMSS, right click on the SQL Server Agent. Under alert system, can you verify that the mail profile is enabled and that the mail system is database mail?
0
 
jpierce26Author Commented:
How do I know if I have the "SMTP Package connection" installed?  I read something about that, but couldn't find if I had it installed.  It wanted to me to install something called the VS2010 Tool.

I do have the correct profile under database mail and it is enabled.  But I believe that profile is under the "management", "database mail".  That I can configure no problem with SMTP and it works fine.  It is the DTS packages that I can't get to use the SMTP profile.  It always defaults to MAPI.

Jeff
0
 
Jared_SCommented:
You should find "SMTP Connection Manager" in your connection managers window in your DTS package. If you edit the connection manager, then you should see the SMTP server name listed there. (Then this connection manager gets referenced in your send mail task.)

Database mail underneath the management folder in SSMS is where your configure the database mail utility. Beyond that, the sql agent can use either database mail OR sql mail.
That's listed in the "Mail Session" section that you can find in the SQL Server Agent properties.

Maybe the mail system is still listed as SQL Mail there.
0
 
jpierce26Author Commented:
Jared_S,

Thanks for sticking with me on this issue.  I am in my "DTS" package window and I only see a "send mail" task.  There is nothing about SMTP in the "Connection" drop down menu.

I also want to mention that when I open an existing DTS package, the title bar in the package is called "DTS 2000 Package Designer"  am I in the right place?

Thanks again,

Jeff Pierce
0
 
jpierce26Author Commented:
Thank you for your assistance.  We ended up migrating to SSIS which was our only solution.

Jeff
0
All Courses

From novice to tech pro — start learning today.