Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-09-09
7
Medium Priority
?
249 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 60

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 60

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 16

Accepted Solution

by:
carsRST earned 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

916 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