Problem using SQL Agent to run SSIS Packages - fai

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

Similar issue on SQL 2008 R2 DB error mentioned below....

Checked the Maintenance Plan and found the subplans were not set to select any particular database, for which the action needs to be performed! And I found one of the databases offline.

Dogh Moment!

Also i set the Ignore Offline Databases.

i re-run the Job & it was successful.

Error Details as below.

Executed as user: **********. Microsoft (R) SQL Server Execute Package Utility Version 10.50.4000.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 9:53:00 AM Progress: 2012-11-12 09:53:00.82 Source: {108E79C2-F5AA-4698-8DD4-**********} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:53:00 AM Finished: 9:53:01 AM Elapsed: 0.734 seconds. The package execution failed. The step failed.

Answer 2

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.

Answer 3

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 4

Use master Go

sp_configure 'allow updates',0 go

reconfigure with override go