September 8, 2020 at 2:10 pm
I'm having a forgetful moment while migrating packages from an existing SSIS server (ServerA) and a new one (ServerB). One of my packages uses the Send Mail task to create a file and email it. Pretty basic. It runs in Visual Studio and as a SQL: Agent job on ServerA without any issues. When testing this, I'm logged into Visual Studio as the service account that runs both the SQL service and SQL Agent on ServerA (sv_ServerA). I verified that sv_ServerA has permissions to the file path being used.
Then I migrate the package to server B where SQL Engine and Agent run as sv_ServerB. I can log into Visual Studio as sv_ServerB and the package executes well. However when I schedule the package on ServerB configured to run as the Agent Service account (which is sv_ServerB), it fails with "Either the file {FilePath} does not exist or you do not have permissions to access the file". I verified that sv_ServerB has access to the file folder and when scheduled as an Agent job, it has enough permissions to create the file in that location. I verified that the step that's failing is the Send Mail task. I verified that the step in the agent job is set to run as the Agent service account.
Is there a difference in how the Send Mail task works between Visual Studio and when run as an Agent job? I feel like I'm missing a very tiny detail here, but I cannot put it together right now.
Regards,
Greg
September 8, 2020 at 5:27 pm
Just shoot in the dark - check that you have read permissions on the drive and parent folders...
September 8, 2020 at 5:55 pm
Bojdar,
It appears that would have been the issue were it not for the fact that the very same package can generate the file to begin with. It was acting like the package was keeping the file open while attempting to email it (which I guess could have caused that same error). That's why I was so focused on it being an AD account issue.
But! Your suggestion made me decide to generate the file on the SSIS server's local drive to rule out any parent level permissions issues.
It turns out that it works just fine on a local drive, it's just the network share that's misbehaving. So, I suspect that you are correct on it being a permission issue at some point further up the folder chain but unless someone complains, I'm just going to keep generating the file and emailing it from a local drive rather than the network share.
Thanks!
Greg
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply