Capturing errors on SQL statement on  Execute SQL task on SSIS

Posted on 2011-10-26
Medium Priority
Last Modified: 2013-11-10

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

Question by:keplan
  • 2
  • 2
LVL 60

Accepted Solution

Kevin Cross earned 1336 total points
ID: 37035003
My understanding albeit limited is to use Error Output options of Data Flow components.
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
LVL 30

Assisted Solution

by:Reza Rad
Reza Rad earned 664 total points
ID: 37048951
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

Author Comment

ID: 37066578
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.

LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 1336 total points
ID: 37066656
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.

Author Closing Comment

ID: 37118565
it solved my problem

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question