Hello,
I have an SSIS package where I use "Execute SQL Task" to pull back several rows of data which I want to send via an email. After the execute SQL task I use a for loop with a script task. However, I am struggling with the c# code. Most of the videos/help I have seen online use msdb.dbo.sp_send_dbmail, but I do not have permissions. I will need to use a send mail task. I feel like I am close. Any help would be greatly appreciated. See code below:
public void Main()
{
Variables varCollection = null;
string header = string.Empty;
Dts.VariableDispenser.LockForWrite("User::EmailMessage");
Dts.VariableDispenser.LockForWrite("User::Field1");
Dts.VariableDispenser.LockForWrite("User::Field2");
Dts.VariableDispenser.LockForWrite("User::Field3");
Dts.VariableDispenser.GetVariables(ref varCollection);
// Construct the email body with HTML format for table
string emailBody = "<html><body><table border='1'>";
emailBody += "<tr><th>Field1</th><th>Field2/th><th>Field3</th></tr>";
// Format the query result within HTML table rows
string message = string.Format("<tr><td>{0}</td><td>{1}</td><td>{2}</td></tr>",
varCollection["User::Field1"].Value,
varCollection["User::Field2"].Value,
varCollection["User::Field3"].Value);
emailBody += message;
// Close the HTML table and body
emailBody += "</table></body></html>";
// Assign the email body to the EmailMessage variable
varCollection["User::EmailMessage"].Value = emailBody;
// Now, send the email
//using (System.Net.Mail.SmtpClient smtpClient = new System.Net.Mail.SmtpClient("xyz.com"))
//{
// using (System.Net.Mail.MailMessage mail = new System.Net.Mail.MailMessage())
// {
// mail.From = new System.Net.Mail.MailAddress("xyz.com");
// mail.To.Add("xyz.com");
// mail.Subject = "Your Subject Here";
// mail.Body = emailBody;
// mail.IsBodyHtml = true; // Set the email body format to HTML
// smtpClient.Send(mail);
// }
//}
Dts.TaskResult = (int)ScriptResults.Success;
}
I have an SSIS package where I use "Execute SQL Task" to pull back several rows of data which I want to send via an email. After the execute SQL task I use a for loop with a script task. However, I am struggling with the c# code. Most of the videos/help I have seen online use msdb.dbo.sp_send_dbmail, but I do not have permissions. I will need to use a send mail task. I feel like I am close. Any help would be greatly appreciated. See code below:
public void Main()
{
Variables varCollection = null;
string header = string.Empty;
Dts.VariableDispenser.LockForWrite("User::EmailMessage");
Dts.VariableDispenser.LockForWrite("User::Field1");
Dts.VariableDispenser.LockForWrite("User::Field2");
Dts.VariableDispenser.LockForWrite("User::Field3");
Dts.VariableDispenser.GetVariables(ref varCollection);
// Construct the email body with HTML format for table
string emailBody = "<html><body><table border='1'>";
emailBody += "<tr><th>Field1</th><th>Field2/th><th>Field3</th></tr>";
// Format the query result within HTML table rows
string message = string.Format("<tr><td>{0}</td><td>{1}</td><td>{2}</td></tr>",
varCollection["User::Field1"].Value,
varCollection["User::Field2"].Value,
varCollection["User::Field3"].Value);
emailBody += message;
// Close the HTML table and body
emailBody += "</table></body></html>";
// Assign the email body to the EmailMessage variable
varCollection["User::EmailMessage"].Value = emailBody;
// Now, send the email
//using (System.Net.Mail.SmtpClient smtpClient = new System.Net.Mail.SmtpClient("xyz.com"))
//{
// using (System.Net.Mail.MailMessage mail = new System.Net.Mail.MailMessage())
// {
// mail.From = new System.Net.Mail.MailAddress("xyz.com");
// mail.To.Add("xyz.com");
// mail.Subject = "Your Subject Here";
// mail.Body = emailBody;
// mail.IsBodyHtml = true; // Set the email body format to HTML
// smtpClient.Send(mail);
// }
//}
Dts.TaskResult = (int)ScriptResults.Success;
}