Who is running the package?

Case
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.