?
SolvedPrivate

SQL Server 2005 DTS Packages using SMTP

Posted on 2013-01-31
10
Medium Priority
?
39 Views
Last Modified: 2016-02-11
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
0
Comment
Question by:jpierce26
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 12

Expert Comment

by:Jared_S
ID: 38839849
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
 

Author Comment

by:jpierce26
ID: 38839901
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
 
LVL 12

Expert Comment

by:Jared_S
ID: 38840175
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:jpierce26
ID: 38840306
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
 
LVL 12

Expert Comment

by:Jared_S
ID: 38840509
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
 

Author Comment

by:jpierce26
ID: 38840535
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
 
LVL 12

Expert Comment

by:Jared_S
ID: 38840700
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
 

Author Comment

by:jpierce26
ID: 38863717
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
 
LVL 12

Accepted Solution

by:
Jared_S earned 1500 total points
ID: 38890865
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
 

Author Closing Comment

by:jpierce26
ID: 38913604
Thank you for your assistance.  We ended up migrating to SSIS which was our only solution.

Jeff
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My client sends data in an Excel file to me to load them into Staging database. The file contains many sheets that they have same structure. In this article, I would like to share the simple way to load data of multiple sheets by using SSIS.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question