SolvedPrivate

SQL Server 2005 DTS Packages using SMTP

Posted on 2013-01-31
10
36 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 500 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Lessons learned during ten years of interviewing for SQL Server Integration Services (SSIS) and other Extract-Transform-Load (ETL) contract roles and two years of staff manager interviewing contractors.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

622 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