SSIS 2008 sp_send_dbmail and .zip files

[Shairal]

I am zipping a file using an Execute Process Task and sending an email with the attachment using an Execute SQL Task (using sp_send_dbmail).

If I do not zip the file, the email works correctly and the attachment (an .xls file) is sent with no problems. However if I zip the file and run the Execute SQL Task, I get the following error:

[Execute SQL Task] Error: Executing the query "EXEC [msdb].[dbo].[sp_send_dbmail] @profile_name ..." failed with the following error: "Failed to open attachment file 'C:\SQLServer2008…\TheFile.zip'. Access denied.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Both .xls and .zip files reside in the same location and have the same permissions. I have confirmed that .zip files are not included in the “Prohibited Attachments” in the Database Mail config. And of course, the zip file is much smaller than the .xls file, so it’s not a file size issue.

I have looked at the Database Mail log, but it does not record the unsuccessful attempts with the zip file.

I still claim ‘newbie status’ and am not sure where to look to see why it won’t email. If you can provide some insight, I would be greatly appreciated!!

 

[MDXer]

Are you getting the file name as a variable? If so trying creating the zip file manually and hardcoding the file name.

 

[Shairal]

I am hard coding the file name in sp_send_dbmail like this: @file_attachments = 'C:\SQLServer2008…\TheFile.zip',

 

[MDXer]

not trying to sound stupid but I hope it isn't as writter with the ... in your package.

 

[Shairal]

No, no "...", it's just a long path so I shorten it.

So I'm gathering that there is no reason why the .zip file is not emailing (like sp_send_dbmail doesn't work with .zip files), is that correct?

Is there a log somewhere that would provide more information as to why it fails? Unfortunately I know just enough to get me through a 'good' day ... any problems arrise and I'm spending hours researching.

Thanks so much for all your time and assistance!

 

[MDXer]

Run profiler and capture the SQL Statement that is being issued. By getting the query being issued you may find that what is being executed in the SQL task is not what you think.

 

[Shairal]

I have done some additional testing and found that I could zip a file from my pc and place it on the server in the folder I'm using and dbmail sends the .zip file out without any problems. (I'm using 7-zip both on the server and my pc)

So maybe it's the way I'm zipping it?? (though it opens up fine, just won't email.) The arguments set up in the Execute Process Task I have are:
a -tzip C:\SQLServer2008\...\TheFile.zip C:\SQLServer2008\...\TheFile.xls.

Any thoughts?

 

[MDXer]

I would zip the file in one task and send it in a seperate task. It may be that the process isn't waiting until the zip task isn't complete when it tries to send.