Catching and Storing Exceptions in SSIS Script Tasks

I use SSIS (SQL Server Integration Services) very often to write data extraction and migration packages. More recently I have been using SSIS to write an interface to update a third party system through calling web services. The SSIS package runs as a scheduled task and queries a database table (call this the interface table) which holds records that have been updated in the source system, that also need updating in the third party system. By using the supplied web services, I am ensuring that all business logic is maintained when updating the third party system, I am also ensuring that no support agreements are invalidated as I am not performing direct updates to the database.

When the package runs, I need to maintain an audit trail of which records were successfully updated and which were not. There are 3 ways in which a record will not be updated, these are:

1. The record from the source system did not exist in the third party system (This should be a rare occurrance as the source system is updated overnight from the third party system).
2. Whilst programmatically the call to the web service was successful, the record could not be updated as the web service returned an unsuccessful response.
3. An exception was thrown in the SSIS package.

Within the SSIS package, I am using a Script Task to perform all the calls to the web services and process the response. The first two items in the list above are handled fairly easily by examining the response coming back from the web service call and updating the status of the record in the interface table.

But what happens if there is an exception thrown in the Script Task in the SSIS package? Well, I would find out eventually that there was an issue, when the customer tells me there is and I have to go and start digging around to find out why the package failed. If I looked in the interface table and there was a status next to the record, I could inform them why the update did not occur. If exceptions are not handled properly, there will be no status information in the interface table saying that an exception has been thrown and I then have to start looking at the job history in SQL Server to find out when and why this happened.

So, how do we handle this in our Script Task? Catching exceptions is easy enough in code and originally I wrote the following code:

try
{
    .. code that could throw an exception ..
    Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception e)
{
    Dts.Events.FireError(-1, "Task Name", e.message, String.Empty, 0);
    Dts.TaskResult = (int)ScriptResults.Failure;  
}

Within the Error Event Handler in the package, I added a simple Execute SQL Task, that used the contents of the System::ErrorDescription variable to update the status message.  Forcing the Script Task to throw an exception (by changing the URL of the web service end-point), the exception information was written back to the interface table, unfortunately all I ended up with as a status was “error in script task” – not exactly useful!! I know there was an error, but I want to know what it was!!

After some further digging and a lot of research, I found a solution to the problem. To overcome the problem, you need to create a package variable that is going to hold the exception message and you need to set this in your catch block. Normally when we work with variables in a Script Task we have to populate the ReadOnlyVariables and/or the ReadWriteVariables lists in the task configuration. For this solution to work, we do not add our package variable to the ReadWriteVariables list.

The code I have ended up with that works is as follows.

try
{
    .. program code that could throw and exception ..
    Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception e)
{
    Variables exceptionVariable = null;
    Dts.VariableDispenser.LockOneForWrite("User::ScriptException", 
                                          ref exceptionVariable);
    exceptionVariable["User::ScriptException"].Value = e.Message;
    exceptionVariable.Unlock();

    Dts.Events.FireError(-1, "Task Name", e.Message, String.Empty, 0);
    Dts.TaskResult = (int)ScriptResults.Failure;
}

Looking at the code above, we have a package variable called ScriptException (remember do not add this to the ReadWriteVariables list!), I then modified my Execute SQL Task for the Error Event Handler to work with the User::ScriptException package variable instead of the System::ErrorDescription variable. When forcing the code to throw an exception, looking at the interface table, the full exception message is now held.

Whilst I don’t want my code to throw exceptions and I always strive for my code to be exception neutral, should the code throw an exception, I can now at least start by looking at the interface table and gain a good understanding of where things went wrong.

 

• Script Task
• Script Task Exceptions
• SSIS
• SSIS Exceptions
• SSIS Script Task Exceptions