July 5, 2007 at 11:18 am
I was wondering if anyone had run into this. I have a stored procedure that returns a recordset and then either generates an error or not based on some criteria. My problem is that if I run this in an Execute SQL Task, the task succeeds regardless of the error being raised. For example:
CREATE PROCEDURE MyTestProc
AS
SELECT GETDATE()
RAISERROR(‘My Error’,16,9)
This procedure will clearly raise an error every time it is run, but the SELECT GETDATE() seems to make the Execute SQL Task succeed. I have worked around this for my issue, but am I crazy, or should a task fail if an error is generated?
July 5, 2007 at 12:14 pm
CREATE PROCEDURE MyTestProc
AS
DECLARE @Err INT
SELECT GETDATE()
SELECT @Err = @@ERROR
IF @Err 0
BEGIN
RAISERROR(‘My Error’,16,9)
RETURN(@err)
END
ELSE
RETURN(0)
Usually setting a return value which is nonzero indicates failure.
July 5, 2007 at 12:22 pm
I tried that and it still did not solve the issue. If I raise the error after the recordset it returned, SSIS does not seem to see the error - regardless of return values.
July 5, 2007 at 2:11 pm
Have you tried assigning the record set to a table variable, testing for errors and then only if not an error returning the table variable as the record set? Just a thought. I'm not sure how to make SSIS see the error, I haven't worked with that technology much yet. I would expect that if you can throw the error before returning the result set you would be able to work around the problem your reporting.
Good Luck
James.
July 5, 2007 at 2:26 pm
Working around my issue was easy, so I don't really have a problem anymore. It was frustrating to find the problem, but that's what I get paid for. I ran the processing in two steps and was able to catch the error in SSIS. I was more curious if anyone else had experienced this and if it was a bug or feature.
I also like to post mysteries now and then to hopefully help the next person that has a similar issue.
July 6, 2007 at 12:05 am
You can set the properties of each task of a SSIS package to either fail the package or allow it to contunue even if errors are found. Perhops you had this set to allow the package to keep running on error? I cant recall the exact options but will try to look it up if you want mreo info on this?
Thanks,
Catherine
Catherine Eibner
cybner.com.au
July 6, 2007 at 5:17 am
I am aware of that option and it was not set.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply