i have ado enumerator on foreach loop and one of the variable contains the file name. following are the task which get executed in for each loop but i'm getting error on delete file task 1. Run a script to create pdf file from ssrs (using filename and path in ado enumerator). Working great. 2. Send email with attached of above pdf file (working great) 3. Now in 3rd step, i want to delete the file, so used file system task and delete file . i'm getting following error [File System Task] Error: An error occurred with the following error message: "The process cannot access the file 'p:\information technology\report output to send\eft\0000023744-EF_DEN1_10-29-10_EFT.pdf' because it is being used by another process.". I'm not sure what is using the file that it is not letting me delete the file. Any clue?mark it as answer if it answered your question :)

here is little more input, if i disable email task, it works fine and delete the file. so not sure how email task is not letting file get deleted mark it as answer if it answered your question :)

I guess files are locked inside one for each loop, Try creating another for loop after that, it might work Gaurav Gupta http://sqlservermsbiblog.blogspot.com/

doesn't sounds right because if i disable email task, it works fine.mark it as answer if it answered your question :)

It sounds like the Email task hasn't released it's lock on the file before the Delete starts. You might try introducing a slight delay between the Send Mail and the File System task. An easy way to do this is to put an Execute SQL task between them, and run the following: WAITFOR DELAY '00:00:02'; That will create a 2 second delay. If you don't want to use an Execute SQL, you can do the same thing using a Script Task that calls the System.Threading.Thread.Sleep() method.John Welch | www.varigence.com | www.agilebi.com | ssisUnit.codeplex.com

Hey Josh, this sounds good but i put the execute sql task with command WAITFOR DELAY '00:00:02'; but it is asking for connection etc. so should i just set any connection and provide this commandmark it as answer if it answered your question :)

anyhow i tried it with 10 sec and 2 sec but no success, same errormark it as answer if it answered your question :)

I'm a little curious. Wouldn't you be able to accomplish the same task with basic subscriptions in SSRS and kicking the SQL Agent jobs that are created from those subscriptions off with sp_start_job and then deleting the file, acciomplish the same thing? This way the call to sp_start_job would be released (completed) and the file locks should be gone?Ted Krueger Blog on lessthandot.com @onpnt on twitter Please click the Mark as Answer button if a post solves your problem!

i understand that but there are some tasks before actual running the ssrs report and creating the file because report is generated dynamically and some data processes happens before it. mark it as answer if it answered your question :)

I figured that part. was just throwing it out there. even a data-driven subscription may be a route and then calling up the job that controls the subscription. Just a thought :)Ted Krueger Blog on lessthandot.com @onpnt on twitter Please click the Mark as Answer button if a post solves your problem!

Yes, it should work with any connection to a SQL Server db.John Welch | www.varigence.com | www.agilebi.com | ssisUnit.codeplex.com

OK, since it seems that the Send Mail task isn't releasing the file, I'd recommend the following: 1) Use a script task to send the email: http://consultingblogs.emc.com/jamiethomson/archive/2006/07/03/SSIS_3A00_-Sending-SMTP-mail-from-the-Script-Task.aspx Make sure to explicitly release any references to the file in the script. 2) File a connect.microsoft.com bug on the Send Mail task, with a repro package.John Welch | www.varigence.com | www.agilebi.com | ssisUnit.codeplex.com

hey john, i'm already using script task to send mail rather send mail task. so not sure what else to release? thanks, pmark it as answer if it answered your question :)

Ah - didn't realize that from the above posts. Can you try using an actual Send Mail task and see what happens? I'd suspect there's a problem in your script code that's not releasing an object, so the lock on the file isn't being released.John Welch | www.varigence.com | www.agilebi.com | ssisUnit.codeplex.com

alright i used smtp email task and it worked but i need to use script to send email because of html email. here is the code for my script and help me to figure out the issue Public Sub Main() ' ' Add your code here ' Dim strFileName As String Dim strFileExt As String Dim strFilePath As String Dim strFullFileName As String Dim strSubject As String Dim strBody As String Dim strVendName As String Dim strPostDate As DateTime Dim strAmount As Double Dim strCurrency As String Dim strToEmail As String Dim strFromEmail As String Dim SmtpServer As String strFileName = Dts.Variables("User::FileName1").Value strFileExt = Dts.Variables("User::FileExtension1").Value strFilePath = Dts.Variables("User::FilePath1").Value strFullFileName = strFilePath & strFileName & strFileExt strVendName = Dts.Variables("User::VendName").Value.ToString strPostDate = Dts.Variables("User::PostDate").Value strAmount = Dts.Variables("User::TranAmt").Value.ToString strCurrency = Dts.Variables("User::CurrID").Value.ToString strToEmail = "info@mycompany.com" strFromEmail = "returnemail@mycompany.com" SmtpServer = "smtp.mycompany.com" strSubject = "Remittance" strBody = String.Format("Here is your remittance for the current pay period (please see attached)</br></br>{0}&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;{1:MM/dd/yyyy}&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;{2:C}&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;{3}", strVendName, strPostDate, strAmount, strCurrency) SendMailMessage(strToEmail, strFromEmail, strSubject, strBody, strFullFileName, True, SmtpServer) strFullFileName = Nothing Dts.TaskResult = ScriptResults.Success End Sub Private Sub SendMailMessage( _ ByVal SendTo As String, ByVal From As String, _ ByVal Subject As String, ByVal Body As String, _ ByVal filename As String, _ ByVal IsBodyHtml As Boolean, ByVal Server As String) Dim htmlMessage As MailMessage Dim mySmtpClient As SmtpClient Dim attach As New Attachment(fileName) htmlMessage = New MailMessage( _ From, SendTo, Subject, Body) htmlMessage.IsBodyHtml = IsBodyHtml htmlMessage.Attachments.Add(attach) mySmtpClient = New SmtpClient(Server) mySmtpClient.Credentials = New System.Net.NetworkCredential("returnemail", "123", "domain") 'CredentialCache.DefaultNetworkCredentials mySmtpClient.Send(htmlMessage) attach = Nothing End Sub mark it as answer if it answered your question :)

alright gentleman, i found the solution. before attach=nothing i added another line it is attach.dispose() and we are good to go thanks for all your help and input. ciao, pmark it as answer if it answered your question :)