April 17, 2012 at 1:23 pm
If I have a stored procedure that returns 0 on success, can I fail if it's not 0? Right now the sql task doesn't return anything, but I'm changing it so I can get the return value - setting the resultset to single row and creating a result set with a name and a variable name.
How can I fail the job if it's anything but zero? I'm thinking I can write an expression in the constraint connecting it to the next piece, but I'm unsure.
thx,
M@
April 17, 2012 at 3:08 pm
Matthew Cushing (4/17/2012)
If I have a stored procedure that returns 0 on success, can I fail if it's not 0? Right now the sql task doesn't return anything, but I'm changing it so I can get the return value - setting the resultset to single row and creating a result set with a name and a variable name.How can I fail the job if it's anything but zero? I'm thinking I can write an expression in the constraint connecting it to the next piece, but I'm unsure.
thx,
M@
Are you controlling the stored procedure code? Or is this an existing stored procedure that reports success or failure through the return code that you have to use, and cannot change?
If you are writing the stored procedure, or can modify it as you wish, then I would not bother using the return code. I prefer to raise exceptions from my stored procedures to report problems to the caller. In this way you would not need to do any special coding in SSIS. If an exception condition were found inside the stored procedure and you raised an exception using the RAISERROR T-SQL function your SSIS would receive the signal and would fail the Execute SQL Task. If you leave the default constraint from your Execute SQL Task to the next Task as "Success" and there were no other parallel tasks in play your package would stop processing at that point.
If you cannot control to the procedure code then I think you are on the right track. Capture the ReturnValue from the proc in a variable and test is in your constraint using an Expression.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 17, 2012 at 3:45 pm
Oh, cool. I didn't even realize I could do that 🙂
Wonder if I should do it since Thursday is my last day. Might hand over your suggestion to the guy taking over for me.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply