sending email from ssis

Question

sending email from ssis

keywestfl9

SSCertifiable

Points: 5215
More actions
February 23, 2010 at 11:50 am

#145509

i have to design a package where i have to extract duplicate accounts from a table and email them .
There might be many rows at a time.
i used execute sql task to write the sql statement but not sure how to pass the output of the query in a email body.
Any suggestion

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 1

What does your result set look like... does it have the Email address... Can you post some more information about what you are trying to do...

Probably you can achieve this storing the values in a recordset destination and Then using a ForEach loop with ADO to send emails out.... but need some more information to guide you in right direction

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Since you already have the execute sql task and a query, why not alter it a bit and use sp_send_dbmail?

http://msdn.microsoft.com/en-us/library/ms190307(SQL.90).aspx

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

sabyasm SSCarpal Tunnel Points: 4324 More actions · Answer 3

[font="Comic Sans MS"]

Alternatively - you can send the output to a flatfile destination (a comma separated file) and mail the file as an attachment.

[/font]

[font="Comic Sans MS"]--
Sabya[/font]

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 4

Or, to give you another method, you can use a script component the compose the e-mail message, store it in a variable and then use the Send Mail Task.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

Using a Script Task you can send email with this code as the basis:

Code to send mail for SSIS:

Dim _oSMTPClient As New SmtpClient("smtpmailhost.yourcompany.com")

Dim _oMailMsg As New MailMessage()

_oMailMsg.From = New MailAddress("someemailaddress@yourcompany.com")

_oMailMsg.To.Add("someotheremailaddress@yourcompany.com")

_oMailMsg.Subject = "The Subject"

_oMailMsg.Body = "Message Body"

_oSMTPClient.Send(_oMailMsg)

Don't forget:

Imports System.Net.Mail

Ganesh Lohani Hall of Fame Points: 3091 More actions · Answer 6

I had also similar requirement last week and I did it

Do the following:

Drag data flow task in control flow and click it

drag oledb source and write the query

like

select acct_no, acct_name, ..... from (your table)

having COUNT(acct_no) > 0

drag row count and connect from oledb source to row count and set a variable in count row.

drag flat file destination to export the data

go back to control flow

drag send mail task and connect it from data flow task

evaluate the variable you created earlier as expression in between dataflow task and send mail task ( in my case i set as

@[User::varDupRow] > 0 in precedence constraint editor)

configure the send mail task and run the package

first data flow task will export duplicate data to flat file.

If found >= 1 row of data, then the send mail task will send email.

Do not forget to attach the file exported by the first task.

I hope it will help although I am not proficient in technical writing.