(this was also posted at http://www.sqlservercentral.com/Forums/Topic1576641-2799-1.aspx)
We are having an issue with 2012 SQL Servers not reporting why a job has failed, this is happening for jobs running SSIS packages in integration services (not the Integration Services Catalog). I have tried to reproduce the error without success and am looking to see if anyone else is seeing this behavior or sees what I am missing.
Usually when you look at the job history for the specific step that failed it will display the error like so:
Source: Execute SQL Task Execute SQL Task Description: Executing the query "select * from madeUpTable" failed with the following error: "Invalid object name 'madeUpTable'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:38:05 AM Finished: 11:38:05 AM Elapsed: 0.421 seconds. The package execution failed. The step failed.
However on some of our jobs we now get a very generic error which makes it very difficult to troubleshoot. If you add an text file in the output for the job step that does receive the whole error, but is set to override at each run (so we lose the error message if the next run is a success).
Executed as user: domain\username. Started: 3:00:01 AM Finished: 4:01:47 AM Elapsed: 3706.97 seconds. The package execution failed. The step failed.
I have tried the following to reproduce the problem, but every time I get the full error message:
- Changed LoggingMode property in the package to disabled, currently set as UseParentSetting
- Changed to execute a stored procedure
- Changed procedure code to be wrapped in a try..catch with raiserror(‘test’,16,1)
- Changed to raiserror(‘test’,18’1) (wondered if there was a smaller range of suitable error numbers)
- Added a Cozyroc task (3rd party) and made that fail
We have this happening on multiple servers, including one that was a new 2012 named instance and one that was upgraded from 2008 R2. We were curious whether the 2008 packages were not upgraded fully/properly with the wizard but this wouldn't be the case for the new 2012 instances.
We are now wondering if this is only happening with certain types of task, but have not managed to find the tasks during testing. Is anyone else experiencing this? Or any suggestions on how to resolve this issue?
Best Answer
SSIS handles errors in two ways.
One is raising an error. The other is failing a task/container/package.
The two are actually quite separate, and you can easily see one without the other.
To bubble an error up to the client (in this case, SQL Agent and its log) you would have to avoid catching the error somewhere. I suspect you might have an error handler there in some, or you have some of those convenient red arrows in there...
Of course, I see many times when people have errors that aren't causing anything to actually fail, so the package just keeps running. But that's another story...