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!