SolvedPrivate

SQL Server 2005 DTS Packages using SMTP

Posted on 2013-01-31
10
34 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
are dynamic queries reliable 2 37
Email Notifications for SQL 2005 9 43
SSIS Feed MS SQL Server Stored procedure with loop 4 71
SSAS Store Forecasting data in the cube 1 59
SQL Server  2012 Release with lots of Enhancements in Database Engine functions, SSIS, SSRS and some of new services like Data Quality Server and Master Data Service. Of particular interest, and the focus of this Article is SSIS. So, time to elab…
A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

752 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