SSIS Send Mail Task keeps Opened Files

Question

SSIS Send Mail Task keeps Opened Files

martinchavezf

SSC Enthusiast

Points: 193
More actions
January 28, 2009 at 9:28 am

#205897

Hi Forum,
I have a SSIS package with the following:
1. A XLS (excel) file is created on "C:\MyFile.xls" from a Query (This is OK)
2. I send these File by using a Send Mail Task (This is OK, the file is sent.)
3. I try to delete the sent file whit a File System Task. This fails because the file is being used by other process. Well, I realized the Send Mail Task keeps opend files doing the following:
1. A XLS (excel) file is created on "C:\MyFile.xls" from a Query (This is OK)
2. I remove the Send Mail Task from the SSIS flow. (The file isn't sent)
3. I try to delete the sent file whit a File System Task. This is working OK, the file is deleted BUT I NEEED TO SEND THE FILE.
The BIG ISSUE is when I send a file with the Send Mail Task, this object keeps my file opened. Why? How can I resolve this? I tried to send the file with CODE using the Script Task and I get the same result: My File keeps opened and I can't deleted.
Best regards.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

martinchavezf (1/28/2009)
Hi Forum,
I have a SSIS package with the following:
1. A XLS (excel) file is created on "C:\MyFile.xls" from a Query (This is OK)
2. I send these File by using a Send Mail Task (This is OK, the file is sent.)
3. I try to delete the sent file whit a File System Task. This fails because the file is being used by other process. Well, I realized the Send Mail Task keeps opend files doing the following:
1. A XLS (excel) file is created on "C:\MyFile.xls" from a Query (This is OK)
2. I remove the Send Mail Task from the SSIS flow. (The file isn't sent)
3. I try to delete the sent file whit a File System Task. This is working OK, the file is deleted BUT I NEEED TO SEND THE FILE.
The BIG ISSUE is when I send a file with the Send Mail Task, this object keeps my file opened. Why? How can I resolve this? I tried to send the file with CODE using the Script Task and I get the same result: My File keeps opened and I can't deleted.
Best regards.

Check this. Let us know if it helps.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

martinchavezf SSC Enthusiast Points: 193 More actions · Answer 2

It didn't work. I wrote my code below. I tried clearing the attachments files but it keeps my file in use.

I tried with these lines too:

myHtmlMessage.Attachments.Clear()

myHtmlMessage = Nothing

mySmtpClient = Nothing

It didn't work.

Dim myHtmlMessage As MailMessage = New MailMessage

Dim mySmtpClient As SmtpClient

Dim str_CCs As String()

With myHtmlMessage

.From = New MailAddress("AjustesPP@dominio.com")

.To.Add("para@dominio.com")

.Attachments.Add(New Attachment(Dts.Variables("User::FilePath").Value.ToString))

.Subject = Dts.Variables("User::MailSubject").Value.ToString

.Priority = MailPriority.High

End With

mySmtpClient = New SmtpClient("170.205.4.168", 25)

mySmtpClient.Send(myHtmlMessage)

myHtmlMessage.Dispose()

Dts.TaskResult = Dts.Results.Success

Any idea?

COZYROC One Orange Chip Points: 28499 More actions · Answer 3

martinchavezf (1/29/2009)
It didn't work. I wrote my code below. I tried clearing the attachments files but it keeps my file in use.
I tried with these lines too:
myHtmlMessage.Attachments.Clear()
myHtmlMessage = Nothing
mySmtpClient = Nothing
It didn't work.
Dim myHtmlMessage As MailMessage = New MailMessage
Dim mySmtpClient As SmtpClient
Dim str_CCs As String()
With myHtmlMessage
.From = New MailAddress("AjustesPP@dominio.com")
.To.Add("para@dominio.com")
.Attachments.Add(New Attachment(Dts.Variables("User::FilePath").Value.ToString))
.Subject = Dts.Variables("User::MailSubject").Value.ToString
.Priority = MailPriority.High
End With
mySmtpClient = New SmtpClient("170.205.4.168", 25)
mySmtpClient.Send(myHtmlMessage)
myHtmlMessage.Dispose()
Dts.TaskResult = Dts.Results.Success
Any idea?

Do you have Symantec AV or some other type of AV installed? The people are talking about problems related with this. Other than that, I cannot find any other reason for this not to work. You can also use a network analyzer to see if the connection is released after the send method is called and the dispose. If not , you have to find out who is holding it.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

martinchavezf SSC Enthusiast Points: 193 More actions · Answer 4

Ready. It works. I have the Kaspersky AV but it doesn't scan my outgoing e-mails. That wans't the issue.I tried with dispose() on Attachments and it worked.

With this:

......

mySmtpClient.Send(myHtmlMessage)

myHtmlMessage.Attachments.Dispose()

myHtmlMessage.Dispose()

myHtmlMessage = Nothing

mySmtpClient = Nothing

.......

I had tried just with:

myHtmlMessage.Dispose()

Regards. Thanks for your help.