SSIS Package runs from Windows, but fails when run from SQL Agent jobs

Question

I have a SSIS package (exports some database tables --> CSV file) After creation of this package, I can run the package from Visual Studio or just by clicking it.

But it fails if i add to execute this package as a SQL Agent Job scheduled at some arbitrary time of a day.

Pointers needed if i have to configure anything related to Permissions/Command line args ??

Thanks in advance

Answer 1

You don't give details of the error message received when the package is run by the Agent, but you need to check the rights of the Windows account under which the SQL Server Agent is running.

If all the package does is export database tables to text files you need to check that the service account has rights to:

1. connect to the database and run select statements on the source tables (if you are using Windows Authentication to control the database connection).
2. Create files in the target location(s) in the file system.

Answer 2

The windows account under which my SQL Server Agent runs belongs to sysadmin Also the same user has admin privileges in my system to access the folders while exporting to the csv file.

The error message in the Job history :

Executed as user: MymachineName\JOBUSER. The package execution failed. The step failed.

In the step i have the SSIS Package included, with correct datasource connections, Run as : SQL Agent Service Account (this is also run under MymachineName\JOBUSER user)