SSIS Package Cannot Send Attachment - Attachment file is invalid

[DBAforever]

My new SSIS package is working to the point of generating an excel worksheet.
But the sp_send_dbmail fails because it cannot find the attachment file. I'm running it from Visual Studio on my laptop. In this situation, which account is it using when it looks for the file? The account the SQL Server engine runs as on the db server?
Thanks.

[yosiasz]

It would help if you showed us some code and gave us more details as to which user account is logged in that is using vs

Also would help to see the actual error

Some of us dont have mind reading powers

[DBAforever]

Good questions, @yosiasz.

I'm logged on as my domain account on my laptop. The file resides on a network drive mapped to my laptop and to the SQL Server where it will eventually run on.

[Execute SQL Task] Error: Executing the query "BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name =..." failed with the following error: "Attachment file T:\Reports\ProductionReport1\ProductionReport1.xlsx is invalid.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLMail',
@body = 'My Report name',
@recipients = 'MyEmailAddy@AAA.com',
@subject = 'My Report',
@File_Attachments = ' T:\Reports\ProductionReport1\ProductionReport1.xlsx';
END

The email sends successfully if I don't include the attachment.

[DBAforever]

This certainly looks like a permissions error.
The agent service runs under account NT Service\SQLServerAgent

I'm going to create a domain account and change the service to use that account and grant that account permissions to the network drive path. That should enable the attachment to send, correct?
Thanks

[Ifor]

Why do you want to waste sql server's resources sending email?

You could just use something like Powershell's Send-MailMessage or SmtpClient in C#. sp_send_email is fine for alerts etc but I would not use it for user reports.

[yosiasz]

but you were running it as yourself from your laptop.
now you just hacking.

Try this as @Ifor recommends

How to send query content as xml zip file to an email according to row

Maybe this using powershell? can also be done with ssis script c# $Username = "MyUserName"; $Password = "MyPassword"; function Send-ToEmail([string]$email, [string]$attachmentpath){ $message = new-object Net.Mail.MailMessage; $message.From = "Micheale@gmail.com"; $message.To.Add($email); $message.Subject = "Monthly Report"; $message.Body = "Greetings, Attached you will find"; $attachment = New-Object Net.Mail.Attachment($attachmentpath); $message.Attachments.Add($a…

[DBAforever]

So the package is able to write the xlsx fiel to the network drive. But then then sp_send_dbmail fails when it tries to send that same file as an attachment....
T:\Reports\ProductionReport1\ProductionReport1.xlsx is invalid. How can I get send mail to work?

[yosiasz]

@DBAforever

I'm going to create a domain account and change the service to use that account and grant that account permissions to the network drive path

So is this after you made the above changes?
And is this you running it from vs etc etc

[DBAforever]

We have not yet created a domain account. So the SQL Server agent is still running as
NT Service\SQLServerAgent. But when I run the VS packsage in Debug mode... it DOES create the excel file on the network drive. It's just the send mail of that file that fails with T:\Reports\ProductionReport1\ProductionReport1.xlsx is invalid. The send mail succeeds when I have the file on the servers C drive.
What do you make of that, @yosiasz ?

[yosiasz]

Cause NT Service\SQLServerAgent has perms to C and not T

[DBAforever]

Yes that's what I thought as well. But I have 2 questions:
Cause NT Service\SQLServerAgent has perms to C and not T

1. The Package is able to export to the external drive T:\Reports\ProductionReport1\ProductionReport1.xlsx successfully
2. But then sp_send_dbmail fails when it tries to send that same file on T as an attachment....
   Are not both processes running under the NT Service\SQLServerAgent or does DB Mail somehow work differently? I don't know how 1 part of the package can find the T drive yet another can't.

Thanks.

[yosiasz]

Read the doc

sp_send_dbmail (Transact-SQL) - SQL Server

[jeffw8713]

Why are you using SQL Server to send the email? SSIS has a task available for sending mail - why not use that one?

As to your issue - SQL Server Agent has no idea about that T:\ drive. If there is a T:\ drive located on the server then it isn't mapped to the same location as the T:\ drive on your workstation. It would be a local drive on that server - and if that is the same location as your T:\ drive then it would be simple to grant permissions to that location on that server.

Instead of using the drive letter - use the UNC path to the network share.

Instead of trying to create a domain account to run SQL Server Agent - create a domain account as a proxy account and run the SSIS package using that proxy account. To test, you can create the proxy account using your credentials - but you can't leave it that way. As soon as you change your password that proxy account will fail.

Once you have a valid domain account setup as the proxy - then you need to grant permissions in the databases and the file system to that domain account.

[yosiasz]

1. The Package is able to export to the external drive T:\Reports\ProductionReport1\ProductionReport1.xlsx successfully

Is this when you are running the package yourself using VS? And is this when you are running it from VS from your laptop or is this when you are logged in on the sql server itself. Very confusing

2. But then sp_send_dbmail fails when it tries to send that same file on T as an attachment....
   Are not both processes running under the NT Service\SQLServerAgent or does DB Mail somehow work differently? I don't know how 1 part of the package can find the T drive yet another can't.
   Same question as above. When you say it tries who or what is this it.

[DBAforever]

@yosiasz and @jeffw8713

1. Yes I am running the package as myself in VS debug mode on my laptop.
   The task generates the excel file onto the T drive
2. The very next task in the package is the send mail for the same excel file and it fails with error: "Attachment file T:\Reports\ProductionReport1\ProductionReport1.xlsx is invalid." And the excel file does exist at that path.

I don't' want to try Powershell or another technique without first understanding why this technique is not working. What am I missing?

[jeffw8713]

sp_send_dvmail is executed in SQL Server on that server. It knows nothing about a mapped drive on your laptop.

Use UNC path to that share and make sure it is open to everyone.

Or you can use the builtin task, but that will fail once deployed because of the same issue. The server does not know about or have mapped drives.

[yosiasz]

a few things are missing

1. you not implementing what @jeffw8713 recommends
2. you not reading the documentation of that function specifically the section on permissions.
   sp_send_dbmail (Transact-SQL) - SQL Server | Microsoft Docs

[DBAforever]

As @jeffw8713 suggested "Use UNC path to that share and make sure it is open to everyone."

That resolved the issue. Now the package is able to email attachments that are stored on the external file share.
Thanks, everyone.