Problem using SQL Agent to run SSIS Packages - fails with "DTSER_FAILURE(1)"

Question

I have an SSIS Package stored in the MSDB database which works fine when I execute it. It will run under SA account or will use Windows security.

I want to schedule this job to run with SQL Agent, but when I do I get a strange error and whatever configuration I try it fails with the following error:

Executed as user: SERVER\SYSTEM. on returned DTSER_FAILURE (1). Started: 09:25:03 Finished: 09:25:04 Elapsed: 0.61 seconds. The package execution failed. The step failed.

The SSIS package itself is reading and writing data to a remote server, but I don't think that it gets that far during the package execution.

Any ideas?

Answer 1

Use master Go

sp_configure 'allow updates',0 go

reconfigure with override go

Answer 2

You will need to grant the account running the SQL Agent windows service the necessary permissions.

Alternatively, you can run individual job steps under another security context by specifying a proxy account to run the step.

Answer 3

Take a look at this KB: http://support.microsoft.com/kb/918760

Especially useful is the last section about error logging - on how to get more detaled error information.