We are getting the file in the source directory. Business
want to maintain the details of the file of these files and send these details to
business to saying that we get these files today.
See the demo
My scenario: we have the source folder where we are getting
daily files and businesses want to store the files information in the database
and send mail to the respective team with details.
Below is the table where we need to store the files
information
|
create table tblFileInfo
(
FileID int identity(1,1),
FileName_tx varchar(100),
FilePath varchar(100),
Extension varchar(10),
FileSize float,
File_CreatedTime datetime
)
|
Taking some variables and script task.
In script task I am writing below code.
|
DataTable dt = new DataTable();
dt.Columns.Add("FileName_tx", typeof(string));
dt.Columns.Add("FilePath", typeof(string));
dt.Columns.Add("Extension", typeof(string));
dt.Columns.Add("FileSize", typeof(float));
dt.Columns.Add("File_CreatedTime", typeof(DateTime));
string[] Files =
Directory.GetFiles(Dts.Variables["User::Source_Folder"].Value.ToString());
SqlConnection
conn = new SqlConnection(@"Data Source=DESKTOP-DQLCKGK\BAGESH;Initial
Catalog=Test;Integrated Security=True");
string
smtpAddress = "smtp.gmail.com";
int portNumber =
587;
bool enableSSL =
true;
string emailFromAddress =
Dts.Variables["User::Email_id_sender"].Value.ToString(); //Sender
Email Address
string password =
Dts.Variables["User::Mail_credentials"].Value.ToString(); ;
//Sender Password
string emailToAddress
=Dts.Variables["User::Email_id_reciver"].Value.ToString();
//Receiver Email Address
string subject =
"******India Sales File Details******" +
DateTime.Now.ToShortDateString();
string body =
"Hello Team, Please find the files details below.";
try
{
conn.Open();
foreach (string
file in Files)
{
FileInfo
file_Info;
file_Info =
new FileInfo(file);
dt.Rows.Add(file_Info.Name,
file_Info.FullName, file_Info.Extension, file_Info.Length,
file_Info.CreationTime);
string sql =
"insert into tblFileInfo values ('"+ file_Info.Name +
"','" + file_Info.FullName + "','" + file_Info.Extension
+ "'," + file_Info.Length + ",'" + file_Info.CreationTime
+ "')";
SqlCommand
cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
}
//Code for sending mail
using
(MailMessage mail = new MailMessage())
{
mail.From =
new MailAddress(emailFromAddress);
mail.To.Add(emailToAddress);
mail.Subject = subject;
mail.Body =
body;
mail.IsBodyHtml = true;
mail.Body +=
" <html>";
mail.Body +=
"<body>";
mail.Body +=
"<table border=2>";
mail.Body +=
"<tr>";
mail.Body +=
"<td bgcolor=#00FFFF>File Name</td><td
bgcolor=#00FFFF>File Path</td><td bgcolor=#00FFFF>File
Extension</td>" +
" <td
bgcolor=#00FFFF>File Size in byte</td><td bgcolor=#00FFFF>File
Created Date and Time</td>";
mail.Body +=
"</tr>";
foreach
(DataRow row in dt.Rows)
{
mail.Body
+= "<tr>";
mail.Body
+= "<td>" + row.ItemArray[0].ToString() +
"</td>";
mail.Body
+= "<td>" + row.ItemArray[1].ToString() +
"</td>";
mail.Body
+= "<td>" + row.ItemArray[2].ToString() +
"</td>";
mail.Body
+= "<td>" + row.ItemArray[3].ToString() +
"</td>";
mail.Body
+= "<td>" + row.ItemArray[4].ToString() +
"</td>";
mail.Body
+= "</tr>";
}
mail.Body +=
"</table>";
mail.Body += "</body>";
mail.Body +=
"</html>";
mail.Body +=
Environment.NewLine + "Thanks & Regards," + Environment.NewLine
+ "ABC Ltd.";
using
(SmtpClient smtp = new SmtpClient(smtpAddress, portNumber))
{
smtp.Credentials = new NetworkCredential(emailFromAddress, password);
smtp.EnableSsl = enableSSL;
smtp.Send(mail);
}
}
}
catch
(System.Data.SqlClient.SqlException ex)
{
string msg =
"Insert Error:";
msg +=
ex.Message;
MessageBox.Show(msg);
}
finally
{
conn.Close();
}
|
Now our
package is ready to run.
Before
running this package records in the table.
Now I am running this package
Package executed successfully.
Records in the table
See mail in the inbox
No comments:
Post a Comment
If you have any doubt, please let me know.