In Part 1 of logging errors I demonstrated how to create an OnError handler that will capture all exceptions and log them. There are times when we expect an error to occur and we may want to simply log the fact and move on or ignore it altogether. For example, the DBA Repository connects to every server, every night to gather statistics from SQL Server. Sometimes a server may be down for scheduled maintenance when the Repository is collecting. In that case, I want to log the inability to connect but to allow the Repository to continue collecting data from the remaining servers without failing the whole package.
Preventing Error Propagation
In a previous post I describe how to loop through a list of servers to gather statistics from the servers. Without error handling specific to each server in the loop I could have a scenario when the Repository is unable to connect and once the failure occurs the package fails and I don’t gather statistics for any remaining servers in the list after the failed server.
I’d rather the Repository log that it couldn’t connect to a given server then move on to the next server and gather everything it can, then I can troubleshoot the connection issue at my leisure.
To accomplish this I created an Execute SQL Task (called Test Source Server Connectivity) that runs no query, just a commented line of T-SQL. The purpose is to attempt to make a connection to the current server in the loop. If the task is successful the control flow moves on to the real work of gathering statistics for the server, however, if the task fails that means the Repository cannot connect.
So, in the Test Source Server Connectivity task I added an OnError event handler which perform the exact same logging task I described in Part 1. Unfortunately, just because I handle the error doesn’t mean the package will continue, I need to configure the package to keep processing.
The System::Propagate Variable
Once you’ve created your error handler you can select the OnError event handler in the properties window. Then in the Variables window select the Show System Variables button, you’ll see one called Propagate in the list:
Set that variable’s value to false. This will prevent the error event from bubbling up to a higher level in the container hierarchy and allow the package to continue functioning.
In Part 1 we created a catch-all to handle unexpected errors in the SSIS package. This allows us to record the exception and fail the package so we can troubleshoot it later. In Part 2 we discovered how to handle known errors, record the fact that something happened and continue processing without failing the whole package. Once we can do this, any exception will be handled and we control when a package fails or not.
That’s pretty versatile…