Reputation: 567
I am wanting to create a Send Mail Task in SSIS inside of an event handler, for when a file in a for each loop does not process as intended. The file is in .xlsx format. In the file name (or in the rows) will be an Account ID that I want to use to reference a table. That table has two columns, AccountID and email address. The email address is where I want to send the email when that file fails to be processed.
I am relatively new to creating these kind of tasks in SSIS so I will need some help. I am mostly worried about how to reference the table in the task... thinking I may need to have multiple steps? Let me know if you need more context or information, and thanks in advance for your help!
Upvotes: 0
Views: 591
Reputation: 5594
Here's an answer to this issue:
I am mostly worried about how to reference the table in the task
Get account number into a variable some how. If it is in filename (this is best target for string manipulation.
Add a execute SQL, and set the SQL to "select email from [your table] where account = ?"
Set result set to single row
Parameter Map you variable from Step 1
Map your result to a "email varaible"
Use that email variable to send mail somehow (either send Mail task or script task or SQL task through DB Mail). My preference is c# script task as you have much more control.
Upvotes: 0
Reputation: 937
From information provided you have to use ssis script task or send email task with variables getting the emailAddress from the reference table.
//SSIS script task
public void Main()
{
SmtpClient varSMTPClient;
string varEmailFrom = Dts.Variables["$Project::projectParameterEmailFrom"].Value.ToString();// readonly variable
string varEmailTo = Dts.Variables["$Project::projectParameterEmailTo"].Value.ToString();// readonly variable
MailMessage varHTMLMAIL = new MailMessage(varEmailFrom, varEmailTo);
varHTMLMAIL.IsBodyHtml = true;
varHTMLMAIL.Subject = "SSIS send e-mail";
varHTMLMAIL.Body = "e-mail body text";
varSMTPClient = new SmtpClient("SMTP Servername");
varSMTPClient.UseDefaultCredentials = true;
varSMTPClient.Send(varHTMLMAIL);
Dts.TaskResult = (int)ScriptResults.Success;
}
Upvotes: 2