Friday, November 30, 2012 11:05 AM
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.
Friday, November 30, 2012 11:59 AM
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).
Sorry for my english. I'm not a native speaker. :)
- Edited by Zdenek Nosek Friday, November 30, 2012 11:59 AM
Friday, November 30, 2012 12:09 PM
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 suggestionI should point out the tasks its failing at are general File proceedures using c# script task. such as copy files, deleting and moving files
- Edited by pauldevinedotnet Friday, November 30, 2012 12:11 PM
Friday, November 30, 2012 1:10 PM
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?