Why does my SSIS package produce different results when executed from a maintence plan

Question

I have a SSIS package that picks up a file in one network location transforms the data and drops off files ready for import in another network location. The package reads a file and writes a new file or overwrites an existing file in another location. The package works as expected from visual studio and when executed manually (double click in explorer). When run from a maintenance plan or scheduled through the SQL server agent job menu the file is always blank. The process will overwrite existing files and create files when no file exists. I believe this indicates it is not a permissions issue.

• Running MSSQL Server 2008r2
• SQL Server Agent is running as NT AUTHORITY\System with privileges provided to the computer$ on the network share.

Any ideas what is going on and why?

Answer 1

It's not that it's executed from a maintenance plan. Rather, it's that the user executing the package is different. NT AUTHORITY\SYSTEM is god on the machine. However, outside the machine, the account is meaningless.

In your case, it attempts to talk to a foreign machine and that request is denied as the share is unlikely to allow Everyone read/write access to it.

I know of at least 3 ways to resolve the problem. The first and worst is to grant Everyone full control of the network share. That would probably allow the local account to do what it needed to on the remote machine but I'm embarrassed to even list that as an option.

The real two options are to change the execution account for SQL Agent to a domain account with sufficient privileges in both the SQL Server Instance and has network access to the share.

A more precise and less risky fix would be to create a create a Credential and then under the SQL Server Agent, create a Proxy that uses the Credentials for job steps of type SSIS Package Execution and then update the job to use the Credentials it is now empowered to access.

As @Shawn Melton pointed out in the comments, a fourth option would be to grant the computer account permissions to the other network resource https://serverfault.com/questions/135867/how-to-grant-network-access-to-localsystem-account

Answer 2

If your file is created but it is empty. You may still have permission issues . Try to use some file monitor program to see what SQL Server (Agent) is doing in your machine and in your remote machine.

Process Monitor is a good utility for this. You can filter file operations and see what your package is doing. You should add this information to your question so that we can help you more.

Also you may try to run your package with different credentials to see if your problem is really about permission issues. A handy utility about this is ShellRunAs.