This is my first time asking a question on StackOverflow, so let me know if you need more clarification. I am attempting to send emails with an attachment to users of a system, however I am having difficulty cleaning up the attachments on the file system after the messages are sent.
Background
I am using SSIS to compile a listing of email recipients and message content from a SQL database, then using a Script Component to do the actual email sending with C#. I am more of a DBA/Project Manager by role, and know enough coding to do some small things, but it has been quite some years since I did any .Net coding on a daily basis.
Each time I run the routine, the attachment files (Excel files in this case) are created successfully in a dedicated directory, then the emails are generated using those attachments and placed in a pickup folder. As each email is created and sent, I want to delete the attachment from its directory, but I get this error:
The process cannot access the file 'D:\mailtest\{filename}.xls' because it is being used by another process.
The specific file that it errors on is different each time, and of the ~3000 emails that are to be generated, it fails at around the 1200-1500 emails mark.
Code
//Create message
using (MailMessage msg = new MailMessage())
{
msg.To.Add(new MailAddress(EmailRecipient));
if (Row.Cc.Length > 0)
{
msg.CC.Add(new MailAddress(Row.Cc));
}
if (Row.Bcc.Length > 0)
{
msg.Bcc.Add(new MailAddress(Row.Bcc));
}
msg.From = new MailAddress(EmailSender);
msg.Subject = MessageSubject;
msg.Body = MessageBody +
"\n" +
"\n" +
this.Variables.EmailFooter;
msg.IsBodyHtml = true;
msg.BodyEncoding = System.Text.Encoding.UTF8;
//Add attachment data
if (File.Exists(attachmentPath))
{
Attachment data = new Attachment(attachmentPath);
data.ContentDisposition.FileName = "Expiring Training.xls";
msg.Attachments.Add(data);
}
if (this.Variables.UsePickupDirectory) //Drops items into pickup directory
{
SmtpClient client = new SmtpClient(SMTPEndPoint, SMTPPort)
{
EnableSsl = false,
DeliveryMethod = SmtpDeliveryMethod.SpecifiedPickupDirectory,
PickupDirectoryLocation = this.Variables.PickupDirectory,
Credentials = new NetworkCredential(UserName, Password)
};
try
{
client.Send(msg);
}
catch (Exception e)
{
//Debug.WriteLine(e);
Row.ErrorMessage = e.Message;
}
finally
{
msg.Dispose(); //Release file
}
}
else //Send to SMTP Server
{
SmtpClient client = new SmtpClient(SMTPEndPoint, SMTPPort)
{
EnableSsl = true,
DeliveryMethod = SmtpDeliveryMethod.Network,
Credentials = new NetworkCredential(UserName, Password)
};
try
{
client.Send(msg);
}
catch (Exception e)
{
//Debug.WriteLine(e);
Row.ErrorMessage = e.Message;
}
finally
{
//Add sleep to prevent sending more than 10 emails per second
System.Threading.Thread.Sleep(100);
msg.Dispose(); //Release file
}
}
}
//Remove attachment file
if (File.Exists(attachmentPath))
{
File.Delete(attachmentPath);
}
Things I've Tried
- Initially, I did not have the using block. I saw this suggestion on a similar SO question, but adding it here seems to make no difference.
- I've added the Dispose() to the finally block of each path (only the pickup directory is used in this case), which I understand should release all locks on the files used as attachments.
- Without either of those things, it fails on the first file encountered, which leads me to believe it is working, but only for a while, and then fails suddenly at a random point during the execution.
- The file specified in the error is not showing in process explorer when I search for it, so maybe it is released quickly after the error, so that I cannot search in time?
- I tried moving the "Delete" functionality to a separate process entirely, running directly after all emails had been sent, but the same message would appear anyway.
I'd be thankful for any advice if anyone has a clue what could be happening.
New info
Adding some extra error handling did improve things, but didn't totally fix it. I've had several runs make it all the way through successfully, and whenever there was an error, it was only on 1 out of the ~3000 files. It did show the same error, though: "The process cannot access the file 'D:\mailtest{...}.xls' because it is being used by another process." That error was from the File.Delete line. It makes me wonder how just by adding more stuff it errors less. Is the sending and deleting happening so fast that it's stepping on its own toes? And throwing stuff in slows it down enough that it's able to keep up?
New Info 2
I took Jamal's advice and added a 500ms delay between send and delete, but only if the first delete attempt failed. So far no errors on 10 straight runs, whereas it was failing every single run in some way prior to adding it. However, the FireInformation message never appeared in the output window, leading me to think it never reached that block, so I'm not sure why adding it seems to work.
//Remove attachment file
if (File.Exists(attachmentPath))
{
try
{
File.Delete(attachmentPath);
}
catch
{
try
{
this.ComponentMetaData.FireInformation(0, "Delete failed", "Trying Delete again after 500ms", "", 0, fireAgain);
System.Threading.Thread.Sleep(500);
File.Delete(attachmentPath);
}
catch (Exception e)
{
Row.ErrorMessage = e.Message;
this.ComponentMetaData.FireInformation(e.HResult, e.Source, e.Message, e.HelpLink, 0, fireAgain);
}
}
}