October 4, 2018 at 11:02 am
I have stored procedure with a structure of begin try / end try ........ begin catch / end catch, and transactions.
As:
begin try
begin tran mytran
.....various things
commit tran mytran
end try
begin catch
if @@trancount > 0
begin
rollback tran mytran
end
end catch
.......Even though I am "handling" the error, I don't want it to be so managed that SSIS doesn't fail the component on error. I would still definitely want SSIS to fail ....in this case anyway..., thoughts? Can I rely on that? Is the answer complicated? I don't say this often, but I'm looking for a "generally speaking, but if your development touches these areas then later will need to consider this and that too" answer if possible. At this point my ssis packages and stored procedures are (relatively speaking), fairly straightforward, not guru weird stuff. 🙂
October 4, 2018 at 12:16 pm
Well one option is to change the return code of the SP if it goes into the catch block and trap for that in the SSIS Package. So you might have something like,
DECLARE @return_code int = 0
try
.......
catch
set @return_code = somethingelse
end
Return @return_code
And then trap for anything besides 0 in your SSIS Package.
October 4, 2018 at 12:27 pm
ok thanks, that's a good option to know about, I hadn't mentally put that together as an option.
ty
October 4, 2018 at 12:39 pm
Or simply:BEGIN TRY
BEGIN TRAN mytran;
--Do stuff
COMMIT TRAN mytran;
END TRY
BEGIN CATCH
IF @@trancount > 0
ROLLBACK TRAN mytran;
THROW;
END CATCH;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 4, 2018 at 1:04 pm
Phil Parkin - Thursday, October 4, 2018 12:39 PMOr simply:BEGIN TRY
BEGIN TRAN mytran;--Do stuff
COMMIT TRAN mytran;
END TRY
BEGIN CATCH
IF @@trancount > 0
ROLLBACK TRAN mytran;THROW;
END CATCH;
well the reason I was still thinking there may be a gap in my design, is, I wasn't sure whether or not that would sufficiently provide enough information to the caller (SSIS package, OLE DB conn mgr) to allow me to fail the component. I do in fact have a rollback in place.
October 4, 2018 at 2:26 pm
ipisors 92539 - Thursday, October 4, 2018 1:04 PMPhil Parkin - Thursday, October 4, 2018 12:39 PMOr simply:BEGIN TRY
BEGIN TRAN mytran;--Do stuff
COMMIT TRAN mytran;
END TRY
BEGIN CATCH
IF @@trancount > 0
ROLLBACK TRAN mytran;THROW;
END CATCH;well the reason I was still thinking there may be a gap in my design, is, I wasn't sure whether or not that would sufficiently provide enough information to the caller (SSIS package, OLE DB conn mgr) to allow me to fail the component. I do in fact have a rollback in place.
I think it will. Should just rethrow whatever error caused the execution to transfer to the CATCH block. The following is a quote from MS docs help:
If the THROW statement is specified without parameters, it must appear inside a CATCH block. This causes the caught exception to be raised. Any error that occurs in a THROW statement causes the statement batch to be terminated.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 4, 2018 at 2:39 pm
But the way I had it without a throw, and with a try/catch block...wouldn't necessarily be reliable to cause a component failure in SSIS? That's what I meant / want to know at this point ....It sort of weighs on me the fact that, before I learned of THROW, I had other procedures, prior, called from SSIS, which had try/catch, and no explicit command to "raise or re-raise an error" inside them. I'm just wondering, for peace of mind, whether that means I opened myself up for the possibility that the try/catch block caught errors, but didn't let SSIS/caller know about them.
If so, I will remember to add something THROW or RAISEERROR at the end of Catch's, if needed, for cases where I want to ensure the caller gets a meaningful [error/failure] signal back.
October 5, 2018 at 5:19 am
ipisors 92539 - Thursday, October 4, 2018 2:39 PMBut the way I had it without a throw, and with a try/catch block...wouldn't necessarily be reliable to cause a component failure in SSIS? That's what I meant / want to know at this point ....It sort of weighs on me the fact that, before I learned of THROW, I had other procedures, prior, called from SSIS, which had try/catch, and no explicit command to "raise or re-raise an error" inside them. I'm just wondering, for peace of mind, whether that means I opened myself up for the possibility that the try/catch block caught errors, but didn't let SSIS/caller know about them.If so, I will remember to add something THROW or RAISEERROR at the end of Catch's, if needed, for cases where I want to ensure the caller gets a meaningful [error/failure] signal back.
You are right to be concerned. By using TRY/CATCH, you are telling SQL Server that you are going to take care of error handling. I suggest that you get into the habit of adding a THROW or RAISERROR() in your CATCH blocks – and revisit what is already deployed to fix that up too.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 2, 2019 at 4:16 pm
I blame a change in employment, corporate email addresses, and resulting having to open 1 new SSC account (just by virtue of having lost the old ones, nothing nefarious!) ::
I just saw your reply now for the first time. Thanks. Glad to know. So it's basically like error handling in object oriented scripting, V.B. etc. That makes total sense to me. I'm so cautiously skeptical of me accidentally attributing VB concepts (my earlier skillset) to SQL, in error, I hadn't really allowed myself to think that was the case.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply