February 23, 2010 at 11:50 am
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
February 24, 2010 at 11:45 am
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
February 24, 2010 at 11:50 am
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
February 25, 2010 at 7:51 am
[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]
February 26, 2010 at 12:18 am
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
February 26, 2010 at 8:56 am
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
February 26, 2010 at 10:28 am
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply