1
Vote

Hanging on Execute SQL Task that returns a single row result but fails

description

If an Execute SQL task fails when returning a single row result (no rows returned to be loaded into the variables), it appears that DTLoggedExec never finishes, and just hangs. I ran a Package.Execute without the custom event handler and the error was logged and the execute method finished. I wonder if it's something to do with the custom event handler?

On 2008R2.

comments

NWest wrote Sep 15, 2014 at 5:47 PM

More information on this. It appears that what's happening is that when the LogEventData method executes, it throws an exception when it iterates over the the collection of variables because the variable that the single row result was supposed to be used in another variable expression. It appears to be getting stuck in an infinite event loop, since the event "The expression for variable X failed evaluation" is caught and then the error is logged, except that during the logging process it tries to evaluate the expression for the variable, throwing another error event, on and on into forever. I'm working on some kind of fix for this.

NWest wrote Sep 15, 2014 at 5:58 PM

Correction - it doesn't throw an exception. Instead, it raises an OnError event. Which is then raised again, and again.

NWest wrote Sep 15, 2014 at 6:14 PM

Ok. I managed to get it working by changing the OnError event to the following:
public override bool OnError(DtsObject source, int errorCode, string subComponent, string description, string helpFile, int helpContext, string idofInterfaceWithError)
        {
            _errorsCount++;
            if ((_selectedEvents & PackageEvents.Error) == PackageEvents.Error)
            {
                if (errorCode != -1073639420) // Error Evaluating Variable Expression - ignore this error otherwise we go into an infinite event loop as we try to log the variable value every time, throwing the event again...
                {
                    LogData("OnError", source, description);
                }
            }
            return base.OnError(source, errorCode, subComponent, description, helpFile, helpContext, idofInterfaceWithError);
        }
Not sure if you need the "return base... " stuff.. return false may be fine.

NWest wrote Sep 15, 2014 at 6:23 PM

It may be prudent to also ignore all of the DTS_E_EXPREVAL* error messages since I think any one of them will cause infinite event calls, eventually using up all RAM until a stackoverflow exception...

Or one could pass in the error code to the log provider, and then check if it's an expression error related to the variables and bypass that piece of logging.

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