June 21, 2012 at 7:24 am
Hi All,
I am currently writing one SSIS job to implement critical business logic and handling external data. However, as the final step in SSIS, I need to check for one specific table with few conditions, and if recordset available, then send an email to support team with those resultset data as an email text file attachment.
Could you please suggest me which one to choose between "sp_send_dbmail" and "SSIS Send Email Task"? WHich option is flexible, secured and most suggested to achieve my email sending requirement?
Thanks in advance.
Regards,
Suresh
Regards,
Suresh Arumugam
June 21, 2012 at 7:44 am
The easier option might be to go down the sp_send_dbmail route, as a mail task in SSIS will require you to export the data to a file, then attach the file to the email task, where as sp_send_dbmail, you can issue the query and have it attach the result as a file on the same command.
But either way they both do the same thing, it just depends how much control you want over it. Personally for something like this I would build a stored procedure which calls the sp_send_dbmail proc that way I only have to change the proc and not have to change the DTSX package deploy it etc, so maintainability is easier I find.
June 21, 2012 at 8:56 am
Both are the same. If you only send email in internal system (your team), you could use the sp_send_dbmail. But if you send email to outside system (your client,...), I suggest you should use a email service for security purpose.
June 21, 2012 at 9:04 am
I have used both in the past. Both do the same thing and it really depends on your level of comfort with each.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 21, 2012 at 9:04 am
Dung Dinh (6/21/2012)
But if you send email to outside system (your client,...), I suggest you should use a email service for security purpose.
Can you elaborate on the security concerns? Also, I don't understand what you mean by "email service" as I would assume that the database engine is using the same email provider/platform as the company.
Jared
CE - Microsoft
June 21, 2012 at 9:16 am
SQLKnowItAll (6/21/2012)
Dung Dinh (6/21/2012)
But if you send email to outside system (your client,...), I suggest you should use a email service for security purpose.Can you elaborate on the security concerns? Also, I don't understand what you mean by "email service" as I would assume that the database engine is using the same email provider/platform as the company.
I mean that it is an application which you can build it (use .Net,..) and register it as a service. When you use the spp sp_send_email, you must configure your database system and you send email directly from database engine I think that we should not do this. It's better you use a service..
As my clients, when they have any promotion, they require the system send email notification to their merchants and they dont allow to send directly from database system.
June 21, 2012 at 9:24 am
Dung Dinh (6/21/2012)
SQLKnowItAll (6/21/2012)
Dung Dinh (6/21/2012)
But if you send email to outside system (your client,...), I suggest you should use a email service for security purpose.Can you elaborate on the security concerns? Also, I don't understand what you mean by "email service" as I would assume that the database engine is using the same email provider/platform as the company.
I mean that it is an application which you can build it (use .Net,..) and register it as a service. When you use the spp sp_send_email, you must configure your database system and you send email directly from database engine I think that we should not do this. It's better you use a service..
As my clients, when they have any promotion, they require the system send email notification to their merchants and they dont allow to send directly from database system.
Ok, but you still haven't addressed any security concerns. What makes your windows service a more secure option than using database mail. Also, SSIS does not require you to have database mail configured.
Jared
CE - Microsoft
June 21, 2012 at 9:25 am
Thanks All for your suggestions and sharing your prior experiences.
I am now confused that i feel we are talking about some thing other than SSIS and DB_Email options to send email.
Basically, it's a retail application and it has to send emails to different stores spread across different state/city of their retail stores.
Please suggest the best suitable option considering this requirement.
Thanks much.
Regards,
Suresh
Regards,
Suresh Arumugam
June 21, 2012 at 9:26 am
Suresh Kumar-284278 (6/21/2012)
Thanks All for your suggestions and sharing your prior experiences.I am now confused that i feel we are talking about some thing other than SSIS and DB_Email options to send email.
Basically, it's a retail application and it has to send emails to different stores spread across different state/city of their retail stores.
Please suggest the best suitable option considering this requirement.
Thanks much.
Regards,
Suresh
Just use sp_send_dbmail if you are building the solution out via tsql.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 21, 2012 at 9:30 am
Thanks for the suggestion and I feel its best suitable option to me.
You guys are really helpful. Keep going.
Regards,
Suresh
Regards,
Suresh Arumugam
June 21, 2012 at 9:50 am
I agree with the sp_send_dbmail proponents. At least that way you can query msdb.dbo.sysmail_allitems to see the status of the emails if something goes wrong. There are also some clever stored procs out there to resend failed emails in the event something goes wrong.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply