SSIS Package OnError Logging

Question

I have an SSIS Package with two steps:

1) FTP in to a server and download a file to the local SQL Server 2) Execute a SQL Satatement, that fires a Storeed Procedure.

Both of these steps work fine, however, I would like to log the success or failure of these steps, so in the system this is for, add a grid with the status (success or failure) of each SSIS Package that runs for that day.

To do this, I have set up an Event handler for the FTP step and added an "Execute SQL Task" to the handler. This uses the "SqlStatementSource" property within Property Expressions, and an insert statement that inserts the system::variables such as System::ErrorCode, System::ErrorDescription.

When I test the expression with the "Evaluate Expression" button, everything is fine. But when I try to test the event by using a wrong password in the FTP connection, the FTP step fails, but the event handler is not raised even though the event handler type is set to OnError.

Any ideas?

Thanks in advance!

Answer 1

What you did seems good, there shouldn't be any problem with that.

Make sure you add the error handler at package level i.e. the top level.

I did the same today, in my case, I was exporting my data to a flat file on local drive and was using a variable for FilePath. I used the OnError event handler too. To check that its working fine or not, I changed the FilePath variable to "Z:\" (which doesnt exists on my system).

and I got the error in OnError event handler like

-1073450982 component "Flat File Destination" (63) failed the pre-execute phase and returned error code 0xC020200E.
-1071636466 Cannot open the datafile "Z:\COM_10212009.txt".

Hope this helps.