SolvedPrivate

SQL Server 2005 DTS Packages using SMTP

Posted on 2013-01-31
10
29 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
  • 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
 

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In couple weeks ago, I encountered an extremely difficult problem while deploying 2008 SSIS packages to a new environment (SQL Server 2014 standard).  My scenario is: We have one C# application that is calling 2008R2 SSIS packages to load text fi…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

759 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now