CTS – Your Technology Partner

SSIS 2012 Script Task Logging

Written by Ben Holley on April 22, 2014

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.

clip_image001

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.

clip_image002

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.

clip_image003

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.

clip_image004

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.

clip_image005

Now, instead of a generic error message, this error is available:

clip_image006

Comments

comments