SSIS Event Handler can't Read User variable

Question

I have an SSIS package that calls a script task to access an SFTP server. The call to that script task includes 2 ReadWrite variables, errorCode and errorMessage. If the Catch block is entered, the error message and error code are set from the exception details and the task return is set to Failure.

On return to the package, execution enters an OnError Event Handler which has a single task which is Execute SQL. That calls a stored procedure to log the issue passing 7 variables.

Currently the Error Code and Error Description passed in are System variables. As the details returned from the script task are in User variables, I changed the Parameter Mapping to the errorCode and errorMessage. When the SFTP call failed, the Event Handler also fails saying that after 16 attempts, it had failed to lock the errorCode variable for Read as the variable was not found.

I don't understand how that's possible as there are other user variables passed in to the same stored procedure that don't cause the problem and when I'm editing the SQL Task, the errorCode and errorMessage variables can be picked.

Answer 1

Hi @Nick Ryan,

Please try the following step-by-step article to achieve what you need.

Capturing SQL Server Integration Services Package Errors Using OnError Event Handlers

https://www.mssqltips.com/sqlservertip/5679/capturing-sql-server-integration-services-package-errors-using-onerror-event-handlers/

The main point is that you don't need to assign values and/or pass around System::ErrorCode and System::ErrorDescription variables. They will be automatically available in the OnError Event handler. UPDATE

Within a catch block, a throw statement with no expression can be used to re-throw the exception that was caught by the catch block. Please see below.

catch (Exception ex)
{
	// Failure
	Dts.Variables["User::errorCode"].Value = 199;
	Dts.Variables["User::errorMessage"].Value = ex.Message;
	
	throw; // re-throw

	result = (int)ScriptResults.Failure;
	Dts.TaskResult = (int)ScriptResults.Failure;
}

Answer 2

The @Yitzhak Khabinsky answer wasn't quite what I was after. Using throw; caused execution to stop, at that point which didn't return the error to my event handler for recording in the logging table.

After a bit more of a search, I came up with this variation of his answer:

                catch (Exception ex)
                {
                    // Failure
                    //Dts.Variables["User::errorCode"].Value = 199;
                    //Dts.Variables["User::errorMessage"].Value = ex.Message;
                    Dts.Events.FireError(199, "An error occured", ex.Message, string.Empty, 0);
                    result = (int)ScriptResults.Failure;
                    Dts.TaskResult = (int)ScriptResults.Failure;
                }

Answer 3

The @Yitzhak Khabinsky answer wasn't quite what I was after. Using throw; caused execution to stop, at that point which didn't return the error to my event handler for recording in the logging table.

After a bit more of a search, I came up with this variation of his answer:

                catch (Exception ex)
                {
                    // Failure
                    //Dts.Variables["User::errorCode"].Value = 199;
                    //Dts.Variables["User::errorMessage"].Value = ex.Message;
                    Dts.Events.FireError(199, "An error occured", ex.Message, string.Empty, 0);
                    result = (int)ScriptResults.Failure;
                    Dts.TaskResult = (int)ScriptResults.Failure;
                }