Raising Error Events

Sometimes the logic in your Script Task can be quite complex, and you may want to raise an error intentionally without causing the entire task to fail. For example, if you try to encrypt a file and the file name doesn’t match what you expect even though your Visual Basic .NET syntax is fine, you may want to raise an error.

You can force the entire task to fail by using the Dts.Results.Failure method, but the error that is logged is not verbose to diagnose why the failure occurred. SSIS would only report a general failure.

In the Microsoft.SqlServer.Dts.Runtime namespace’s interface, IDTSComponentEvents contains events that can be wired within the Script Task. When an error occurs, the FireError method can be used to raise the error. The parameters shown in the following table are passed to expose information about the error that occurred.

Open table as spreadsheet

PARAMETER	DATA TYPE	DESCRIPTION
errorCode	Integer	Unique identifier of the error message
subComponent	String	Specific information as to where the event originated
Description	String	Explanation about why the error occurred
helpFile	String	Location of the file that gives specific information about the error
helpContext	Integer	Unique identification of the information source within the Help file

The following example shows how to throw an error through code:

  Dts.Events.FireError(1234, "", "Here is an error!", "", -1) 

After the code has been run, the error can be seen by switching over to the Progress tab during execution (see Figure 2-12), or to the Execution Results Tab within the design view of the package. It can also be seen in the SSIS Logs if you are producing those. This error will be seen whether the task would have succeeded or not.

Figure 2-12: Viewing the error using the Progress tab during execution

When using error events, there are two important things to think about when deciding what information the event should know about:

• What type of error occurred?

• What information about the error needs to be raised?

When addressing errors, utilize the Try…Catch block. Within the Try…Catch block, errors can be caught by the type of error that is thrown. For example, in Listing 2-2, connections are opened and data is returned from the database. If an error is thrown when performing either task, a SqlClient .SqlException can be thrown and handled differently than if any other type of error is thrown.

The following code demonstrates how to handle the two different types of errors. If a SqlClient .SqlException occurs, then an error event is fired. If a standard exception is thrown, then a message box is raised to show the exception message. You obviously do not want to normally pop up message boxes in SSIS. Otherwise, you could inadvertently leave them, and your job would hang in production because no one is there to click OK.

  Try     'Do some stuff that may fail... Throw New Exception("Here is a base Exception") Catch Sqlex As SqlClient.SqlException     Dts.Events.FireError(Sqlex.ErrorCode, "", Sqlex.Message, "", -1) Catch ex As Exception     MsgBox(ex.Message) End Try