We are getting the bunch of CSV files into the processing directory and we need to load these files into the database. Sometimes the client sends some empty (0kb) files. Our requirement is to load the non-empty files to the database and for the empty files we need to log this information into the table and send this information to the client.
Let’s see how we implement.
Below is the processing directory
We have some 0kb files. My requirement is to load Non-0kb files to the database and load 0kb files mail to the client saying that these files are 0kb files, please send it aging these files.
Taking Foreach look container and taking file enumerator
It will iterate the file one by one.
In the Foreach container, we will take the script task and check the size of the files. If size is greater than 0kb then we will load the data into the table.
We need to create some variables.
Inside the script task, we are getting the file information.
int filesize = 0;
FileInfo oFileInfo = new FileInfo(Dts.Variables["User::File_path"].Value.ToString());
Dts.Variables["User::File_Size"].Value = oFileInfo.Length.ToString();
Dts.Variables["User::File_name"].Value = oFileInfo.Name.ToString();
filesize = int.Parse(oFileInfo.Length.ToString());
if (filesize==0)
{
Dts.Variables["User::IsEmpty"].Value = true;
}
|
Here I am setting the IsEmpty flag. If the file size is 0 kb then the value is true by default value is false.
If the file is empty in this case execute SQL task will execute
In the Execute SQL task, we are inserting the empty file details.
Below is the table
CREATE Table fileInfo
(FileID int identity (1,1),
FileName varchar(50),
FilePath varchar(100),
Filesize varchar(20),
CreatedDate datetime,
CreatedBy varchar(50)
)
|
Configure the execute sql task and provide the sql statement.
Map the parameter.
If the file is not empty in this case Data Flow task will execute.
In data flow task we will load the data.
I am loading the data into the Customer table.
Once enumerate all files I am checking any empty file present in the table. I am getting the count from the table.
If the count is greater than 0 then I am pulling the data from the fileinfo table and send the mail.
In the expression, I am checking the count of the error file
Pulling the data from the Fileinfo table and storing it into the object variable.
Soring the result in the object variable.
Once we get the file info with the help of script task I send mail.
Below is the code for sending the mail in HTML format
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 = "Empty Files";
string body = "Hello, Below files are empty. Send it again.";
DataTable dt = new DataTable();
var oleDa = new OleDbDataAdapter();
oleDa.Fill(dt, Dts.Variables["User::FileInfo"].Value);
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>";
mail.Body += "<tr>";
mail.Body += "<td>FileName</td><td>FilePath </td><td>Filesize</td><td>CreatedDate</td>";
mail.Body += "</tr>";
foreach (DataRow row in dt.Rows)
{ mail.Body += "<tr>";
mail.Body += "<td>" +row.ItemArray[0].ToString() + "</td><td>"+ row.ItemArray[1].ToString() + "</td><td>"+ row.ItemArray[2].ToString() + "</td><td>"+ row.ItemArray[3].ToString() + "</td>";
mail.Body += "</tr>";
}
mail.Body += "</table>";
mail.Body += "</body>";
mail.Body += "</html>";
using (SmtpClient smtp = new SmtpClient(smtpAddress, portNumber))
{
smtp.Credentials = new NetworkCredential(emailFromAddress, password);
smtp.EnableSsl = enableSSL;
smtp.Send(mail);
}
|
Now Package is ready to run.
I am executing this package.
OOOOOOOOOOO my god, send mail failed.
It may be the cause of below
Case 1: when the password is wrong
Case 2: when we try to log in from some App
Case 3: when we try to log in from the domain other than your time zone/domain/computer (This is the case in most of the scenarios when sending mail from code)
I need to set less secure app access on
Now I am turning on
Now I am running this package.
Package executed successfully. See the mail
I am getting below error in script task,could you please help me
ReplyDeleteerror:
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()