2

(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?

Improve this question

1 Answer 1

Reset to default
2

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...

Improve this answer
1
  • I have a similar issue. I am pretty new to SSIS and have inherited a very simple package that imports data from a MySQL database into a SQL Server table. If the Data Flow Component (ADO.NET Source) that runs the SELECT in MySQL gets a timeout, the Agent Job step that executes the package only provides the generic error and doesn't say anything about the timeout. There are no red arrows or any other obvious error handling mechanisms in the package. How can I get the timeout error to bubble up to the Job Agent History?
    – Mark Freeman
    Commented Oct 17, 2014 at 15:20

Not the answer you're looking for? Browse other questions tagged or ask your own question.