One of the great features of SSIS 2012 is the built in logging for packages stored in the SSIS catalog. It is a true time saver but there is one small shortcoming.
If you have a runtime error in a script task, you get one error message for all possible root causes:
Script Task:Error: Exception has been thrown by the target of an invocation.
This error message is not very helpful when you are trying to fix a production issue in the middle of the night.
Here is a solution we found for the issue. It requires changes to the script task and adding additional logging for script tasks.
Part 1: The Script Task
The first thing to do is to add the ServerExecutionID System variable to the ReadOnlyVariables for the Script Task. The ServerExecutionID variable provides the Operation ID that identifies each package execution in the SSIS built-in logging.
The next step is to add an ErrorHandler (or Try-Catch in C#) to Handle the error. In the ErrorHandler Dts.Log is used to log the error.
Part 2: Logging
The next part is much like logging in the pre SSIS 2012 world. First, a logging provider must be set up. I prefer to log to a SQL Server so that custom error reports can be created that join both sets of logging. As a default, the msdb database will contain the sysssislog table that was used in the previous version of SSIS. To open the Log configuration dialogue, select “Logging” from the SSIS menu at the top of the Visual Studio window.
Next, the ScriptTaskLogEntry Event needs to be selected on the Details tab. This is what allows Dts.Log to write a custom message to the logging table.
Now, instead of a generic error message, this error is available: