Solved

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

Posted on 2011-09-09
7
238 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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:huslayer
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

747 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

13 Experts available now in Live!

Get 1:1 Help Now