SSIS SQL 2012 Package Execution Issue

Question

Hi guys, 

Need some help this baffling me, I have a SSIS package which was upgraded from sql 2008 R2 to SQL 2012.

I can Execute successfully it in SQL Server Data Tools.

I can Execute successfully it using the Execute Package Utility.

It failes execution running as a scheduled Job in SQL Server Agent Jobs.

Any help would be appreciative, the error is vague than i'm used to.

It fails execution with the following error, taken from the history of SQL Server Agent Jobs.

Date		11/30/2012 9:59:11 PM
Log		Job History (Gears v3.0)

Step ID		1
Server		AMAZONA-953R1CO
Job Name		Gears v3.0
Step Name		Gears v3.0 - SSIS
Duration		00:00:13
Sql Severity	0
Sql Message ID	0
Operator Emailed	
Operator Net sent	
Operator Paged	
Retries Attempted	0

Message
Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility  Version 11.0.2100.60 for 32-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  9:59:11 PM  Error: 2012-11-30 21:59:24.56     Code: 0x00000001     Source: Boom API      Description: Exception has been thrown by the target of an invocation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  9:59:11 PM  Finished: 9:59:24 PM  Elapsed:  13.01 seconds.  The package execution failed.  The step failed.

Answer 1

Hello,

Have SQLserverAgent account rights for managing sources used in package? If you run it in execute package utility it runs under your account.

Try to crate proxy account for SSIS in SQL agent and use it for Job step (dropdown execute as).

http://msdn.microsoft.com/en-us/library/ms190698(v=sql.105).aspx

Zdenek

---

Sorry for my english. I'm not a native speaker. :)

Answer 2

Hi There,

I tried the proxy account using my log in but get the same error.

How would I go about checking whether SQLserverAgent has account rights for managing sources?

Much appreciated for your suggestion

I should point out the tasks its failing at are general File proceedures using c# script task. such as copy files, deleting and moving files

Answer 3

Ok, I seem to have fixed it.

There was an error in the code, a file i was attempting to delete wasn't closed. So i had to close it first.  Given this new sql 2012 is much much faster I suspect the bad coding worked on my old server because garbage collection went active before the next step, whereas here things are happening faster. I assume that they are much more optimised when run from within sql server agent?