Send the excel report attachment in mail using script task

We have a requirement that we need to send the excel report file to the business through email attachment.

 Below is the excel report.  

File data   

We are using the below Package to Generate this Excel Report.

Load data in pre-formatted excel file in SSIS

https://bageshkumarbagi-msbi.blogspot.com/2016/02/load-data-in-pre-formatted-excel-file.html

To send the attachment we are taking the script task and writing the C# code to send the attachment. 

Taking a variable to store the file name with full path. 

Wringing the below script to send the attach mail.

//using these namespaces using System; using System.Data; using Microsoft.SqlServer.Dts.Runtime; using System.Windows.Forms; using System.Net.Mail; using System.Data.OleDb; using System.Net;  //code public void Main()                                 {             // TODO: Add your code here              string smtpAddress = "smtp.gmail.com";             int portNumber = 587;             bool enableSSL = true;             string emailFromAddress = "***************@gmail.com"; //Sender Email Address             string password = "***********"; //Sender Password             string emailToAddress = "*********@gmail.com"; //Receiver Email Address             string subject = "Sales Report";             string body = "Hello team, please find attached reports.";             DataTable dt = new DataTable();             var oleDa = new OleDbDataAdapter();              using (MailMessage mail = new MailMessage())             {                 mail.From = new MailAddress(emailFromAddress);                 mail.To.Add(emailToAddress);                 mail.Subject = subject;                 mail.Body = body;                                mail.Attachments.Add(new Attachment(Dts.Variables["User::File_path"].Value.ToString()));                   mail.IsBodyHtml = true;                            using (SmtpClient smtp = new SmtpClient(smtpAddress, portNumber))                 {                     smtp.Credentials = new NetworkCredential(emailFromAddress, password);                     smtp.EnableSsl = enableSSL;                     smtp.Send(mail);                 }             }                 Dts.TaskResult = (int)ScriptResults.Success;                                 }

  Now running this package. 

Executed successfully.

See the sender send mail.  

Receiver inbox 

See the mail body and attachment. 

Got the expected mail.