Capturing errors on SQL statement on  Execute SQL task on SSIS

Posted on 2011-10-26
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
    LVL 59

    Accepted Solution

    My understanding albeit limited is to use Error Output options of Data Flow components.
    It should point you to

    Here is another reference on Technet
    LVL 30

    Assisted Solution

    by:Reza Rad
    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

    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 59

    Assisted Solution

    by:Kevin Cross
    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

    it solved my problem

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    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.

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now