Why doesn't the error show up in the SQL Log?

Question

Why doesn't the error show up in the SQL Log?

Rod at work

SSC-Dedicated

Points: 34017
More actions
August 1, 2024 at 8:14 pm

#4441751

We've got a third party app that runs on SQL Server 2008 R2. There's a SQL Job which runs two SSIS packages. (I am not a DBA, and I don't know how to develop SSIS.) Anyway, this SQL Job has been failing for a few days, but there's no error in the job's history. From it's point of view, everything is fine. But if I open the latest history and look at it, I'll see this:
Started: 7:05:06 AM
Error: 2024-08-01 07:05:31.87
Code: 0xC0047062
Source: Validate Addresses in LabELRHold Call Address Validation Web Service [361]
Description: System.NullReferenceException: Object reference not set to an instance of an object.
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper100 wrapper, Int32 inputID, IDTSBuffer100 pDTSBuffer, IntPtr bufferWirePacket)
End Error
So, why doesn't that show as an error in the SQL Job's history?
Kindest Regards, Rod Connect with me on LinkedIn.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

This is one of the reasons why I generally don't care for "Try/Catch" in code in relation to jobs and database code. If people don't write it correctly, the calling code will never know there was a failure or may log something somewhere and still not cause an error. Even if it does, it's still possible on jobs to identify what actions need to be taken for each step including jumping to other steps. If the final step they're jumped to isn't setup correctly, the job may never fail.

A whole lot of "third party database/SSIS 'apps' " fall into such a category.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

SSIS doesn't do a great job of logging by default.

These might help:

Rod at work SSC-Dedicated Points: 34017 More actions · Answer 3

That could be, Jeff, I don't know. I've never written a SSIS package and I have idea how to get started at it. Anyway, the vendor may have done that, as you say.

Kindest Regards, Rod Connect with me on LinkedIn.

Rod at work SSC-Dedicated Points: 34017 More actions · Answer 4

Thank you, Steve. I'll pass those links along to my DBAs

Kindest Regards, Rod Connect with me on LinkedIn.