Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

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

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
vbnetcoder
Asked:
vbnetcoder
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
vbnetcoderAuthor Commented:
>>  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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
carsRSTCommented:
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
 
Jason Yousef, MSSr. BI DeveloperCommented:
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
 
PedroCGDCommented:
vbnetcoder,
Did you tried SSIS Event Handlers?
Regards,
Pedro
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now