Stored procedure and SSIS interaction

Hi,
I have stored procedures that extract data from SQL table.. In these procedures we have various conditions and for some conditions if they are met we have a GOTO which points to an Error and Prints the custom error..
So according to the stored procedure that is an error and it prints it out.


here is what it looks like
our code begins...
 here is our code.....
.......
then half  way thru the code this is part of the code...suppose this condition occurs then goto ERROR2
IF (@LowDate >= @HighDate)
            goto ERROR2

more code follows.....
....
at the end of the code these are the various error values which get pointed at..
RETURN

      ERROR1:
      PRINT 'Procedure ERROR : Since ManualOverride, Please Enter Both Date Parameters'
      RETURN 10
      
      ERROR2:
      PRINT 'Procedure ERROR : Please ensure that the Low Date is earlier than the High Date'
      RETURN 11

      ERROR3:
      PRINT 'Procedure ERROR : High Date cannot be null'
      RETURN 12
      
      ERROR4:
      PRINT 'Procedure ERROR : Low Date MUST be null if doing a regular run'
      RETURN 13
END



 In SSIS i am calling these procedures with the Execute SQL task,

So my question is now since some condition occurs and then in stored procedure it will GO TO and ERROR and print that Error, thats something like an error and should not happen.. so would this be still executed by SSIS (considering it as a succesful run of stored procedure )and the package continues to run or does SSIS throw an error too when this GO TO ERROR occurs in stored procedure..
esotericmeeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Reza RadConsultant, TrainerCommented:
these error lables is not sql server errors, so execute sql task will not find them as error.
if you want to do better error handling it's better to use RAISERROR command instead of print a message as error.
http://msdn.microsoft.com/en-us/library/ms178592.aspx

0
esotericmeeAuthor Commented:
So having RaiseErrors in my stored procedure for occurrences of these events will consider them as errors?

and  upon finding these errors in execution of these procedure from Execute SQL task,  the SSIS package will then fail?

Thanks for your help
0
Reza RadConsultant, TrainerCommented:
Yes, RaiseError will led to errors, but be careful about severity in raiserror.
severity from0 to 10 will not led error, just led messages,
severity from 21 to above will led fatal errors

for your case it's better to use severity between11 to 19 , for example 11 is good one.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
esotericmeeAuthor Commented:
That just worked
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.