CODEWITHSUNDEEP
ssis
user2703790
user2703790

Reputation: 153

Handing SSIS validation failures

I'm trying to implement the SSIS package failure handler. I've added onError Event handler on whole package and failure operation the task where i'm expecting an error in the control flow.

The main task of the package is to pick up some data from the db, the error handler is Send Mail Task sending email message with error description.

Now, I'm trying to emulate a common error: I make some changes in the SQL query (for example, set incorrect name for one of the columns) and try to run the package.

What i see is that package fails on validation stage and does not even proceed to execution, saying

Error:  SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occured. Error Code 0x80040E14. An OLE DB record is available. Source "Microsoft SQL Server Native Client 11.0"     
Hresult 0x80040E14 Description "Statement(s) could not be prepared"
Description "The multipart identifier "MyTable.SomeWrongField" could not be bound". 
Error: "OLE DB Source" failed validation and returned validation status "VS_BROKEN".

It doesn't even procceed to execution, failing right after validation. How to set the error handle so it handles EVERY error, even the issue like this (incorrect sql request)?

Thanks.

Upvotes: 1

Views: 2221

Answers (1)

billinkc
billinkc

Reputation: 61211

A Validation error is the SSIS equivalent of a syntax error. There is no opportunity for recovery as the execution is dead in the water. OnTaskFailed and OnError won't catch this type of error (which is one of the reasons we no longer implement error notification from within our packages themselves)

Upvotes: 1

Related Questions