question

KeshaShah-3519 avatar image
0 Votes"
KeshaShah-3519 asked ZoeHui-MSFT answered

Unexpected behavior of OnError event

Hello everyone,

I am new to SSIS and am looking for some guidance to better understand the working.

I've configured the OnError event at Package level, which has some logging related tasks. In the ControlFlow, I have placed an Execute SQL Task and have called a StoredProcedure in it. Now, when I execute this package, OnError event is called twice. I am not sure why but it looks like, it's being called once for the error generated within StoredProcedure and then again because the ExecuteSQL task fails. Below are the 2 error messages -
1. Error: Cannot insert the value NULL into column 'Column Name', table 'Table Name'; column does not allow nulls. INSERT fails.
2. EXEC [StoredProcedureName..." failed with the following error: "The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Now, in order to make it call the OnError handler only once, I used TRY, CATCH and THROW in the ExecuteSQL task to wrap the call of the StoredProcedure, and it worked as expected and generate below error:

EXEC [dbo].[StoredProcedureName..." failed with the following error: "Cannot insert the value NULL into column 'Column Name', table 'Table Name'; column does not allow nulls. INSERT fails.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.


Can you help me understand this behavior?

Thanks!

sql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered

Hi KeshaShah-3519,

Could you please share the StoredProcedure with us?

Details about Event Handlers you may refer: integration-services-ssis-event-handlers

Regards,

Zoe


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.