Solved

Intergration services SSIS ---- handeling Execute SQL task errors

Posted on 2011-09-09
7
242 Views
Last Modified: 2013-11-10
I have a bunch of SQL tasks on my control flow. If any one of them fails i want to trigger an error event that will popup a message box (for now) contain the name of the execute sql task that failed, the time, & the reason using a script task.

What would the code look like?
0
Comment
Question by:vbnetcoder
7 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36517674
Since you have not gotten any responses yet, see if this helps:
http://msdn.microsoft.com/en-us/library/ms141679.aspx
You can try setting each tasks error output to another custom task written in say .NET that does your popup window.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36517692
Some other thoughts while you wait:
How to: Add an Event Handler to a Package > http://msdn.microsoft.com/en-us/library/ms139744.aspx
Notify Operator Task > http://msdn.microsoft.com/en-us/library/ms140060.aspx

You can add an Event Handler and in theory using a Notify Operator Task by setting the message property through code to the error details. One of these days, I will stop saying I am going to use SSIS more and actually use it. :)
0
 

Author Comment

by:vbnetcoder
ID: 36517696
>>  have added the SSIS zone to your question to see if it will help attract some of those Experts

Thank you. I looked for a SSIS group but did not find it.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 16

Accepted Solution

by:
carsRST earned 500 total points
ID: 36522185
SSIS has logging build in to capture what went wrong.  You can log different items, but you'll specifically want to select the option of "OnError" for your purposes.  You'll get a detailed list of things that screwed up in a nice text file.  Just run your package and open the text file immediately after.

See this link.

http://pragmaticworks.com/Products/Business-Intelligence/BIxPress/ssis-logging-auditing-monitoring.aspx


However, if you want do what to set an action to happened on an error, you can set a "precedent constraint."  See link below for a good explanation.
http://www.simple-talk.com/sql/ssis/working-with-precedence-constraints-in-sql-server-integration-services/
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 36529033
HI,

As CarsRST said, you could use the logging that comes out of box in SSIS or roll your own framework and call the error in your e-mail msgs.

example of the framework will be:

http://amenjonathan.wordpress.com/2010/11/14/creating-a-solid-ssis-etl-solution-part-1-package-and-error-logging/
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 36590097
vbnetcoder,
Did you tried SSIS Event Handlers?
Regards,
Pedro
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.
Viewers will learn how the fundamental information of how to create a table.

829 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