Capturing errors on SQL statement on Execute SQL task on SSIS

Hi,

Can anybody suggest the best practice to capturue any error on SQL statement embaded on the EXECUTE SQL SCRIPT on SSIS?
It could be data error or any errors.
I added TRY and CATCH, but not sure that is the best practice. I've a Email task setup as the error path on the control flow which should capture the errors on the EXECUTE SQL TASK.

Thanks in advance for any help


keplanAsked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
My understanding albeit limited is to use Error Output options of Data Flow components.
http://msdn.microsoft.com/en-us/library/ms141679.aspx
It should point you to http://msdn.microsoft.com/en-us/library/ms140083.aspx

Here is another reference on Technet http://technet.microsoft.com/en-us/library/cc304419.aspx
0
 
Reza RadConnect With a Mentor Consultant, TrainerCommented:
you can use OnError event handler of  the Execute SQL Task ,
and there use ErrorCode and ErrorDescription system variables to fetch full description of error and use these variables in send mail task or any other task
0
 
keplanAuthor Commented:
that mean, I should not use TRY and CATCH block inside the SQL task? isn't it? if I use that statement, the error want capture on  OnError Event?
Please give advice on this.

ThankS
0
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
Correct. If you want to have your data flow aware of the error, you will need to let the exception flow through the data flow error output.
0
 
keplanAuthor Commented:
it solved my problem
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.