Solved

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

Posted on 2011-09-09
7
239 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

920 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

11 Experts available now in Live!

Get 1:1 Help Now