In the SSIS MSDN forum it's a recurring question when a package works in BIDS (Visual Studio), but doesn't work when it is running as a job within SQL Server Agent: Who is running the package?
Solution
I will show you a couple of ways to start a package and let you see which account is actually running the package. For this experiment I created two extra users on my VPC. Besides Joost (the one I use for login to Windows), there are now Joost2 and Joost3.
 |
| Extra users |
And I created a very simple package with one Execute SQL Task that inserts the system variable System::UserName into a table. This system variable shows the account of the user who started the package.
 |
| The insert query |
 |
| The parameter for the query |
Now I'm starting this package in various ways.
1) Running the package in Visual Studio / BIDS
Starting the package in BIDS shows that my user (which I used to login) is running the package.
 |
| Running in BIDS |
2) Running the package as a job under SQL Server Agent Service Account
I created a job in SQL Server Agent with one jobstep that executes the SSIS package. I'm using the default SQL Server Agent Service Account to run this step (see screenshot).
 |
| Job step running under Service Account |
If you go to services (run services.msc) and search for SQL Server Agent, you can see the actual account that is used. For testing purposes I changed the user that runs the Windows Service SQL Server Agent, to Joost2.
 |
| Windows Service now running under Joost2 |
Second run of this package and now via the job. When I excute the job, it shows that Joost2 is running the package.
 |
| Joost2 is running the package |
3) Running the package as a job under a proxy
Now I will execute this job a second time, but this time I will create a proxy and a credential under user Joost3 for it, so that is doesn't run under the default service account.
First creating the credential.
 |
| A credential under user Joost3 |
And second the proxy that is using the newly created Credential. And the proxy can only run SSIS packages.
 |
| A proxy running under the new credential |
And third I will edit the job step and select this new proxy under "Run as:".
 |
| The job step running under proxy |
And when I now start the job you will see that the package runs under Joost3.
 |
| Third time running the package, second time running the job |
Conclusion
If for example you get authorization issues when running a package via a Job or the foreach loop doesn't return any files when running as a job... Then you should check who is actually running the package (service account or a credential via a proxy) and then check the difference between that account and your login account that is used by BIDS.
