Copy-and-Paste SSIS Execution Monitoring with Failure-Alert Email

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Stan Kulp » Copy-and-Paste SSIS Execution Monitoring with...

18 posts, Page 2 of 2

««1 2

Copy-and-Paste SSIS Execution Monitoring with Failure-Alert Email

Rate Topic

Display Mode

Topic Options

Author

Message

Bill Galashan

Posted Wednesday, July 14, 2010 1:32 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 12:33 PM
Points: 124, Visits: 204

If you can code something no matter how, then you can probably include it in the available event handlers. You have the ability to run just about anything you want and vb scripting will probably provide the simplest ways of doing so through the internal event handlers built in to SSIS

Phil identified exactly what I was tactfully avoiding saying in that you seem to have dived in without knowing what the tool set actually offers. I see this time and time again and while many a time this can result in a reasonably elegant solution it is in fact a re-invention of the wheel and I am not convinced that employers will see this as best use of ones time.

The SQL Server tool set is complex and as you say there are a myriad number of ways to solve a problem but generally there is always a best technical solution to address a problem rather than a technical solution looking for a problem to solve.

The only way you can provide best technical solutions to a problem is by a very detailed knowledge of the tool set and hence the best way to solve any specific problem.

Post #952664

fatehbourghoud

Posted Thursday, July 15, 2010 2:32 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, January 28, 2013 7:46 AM
Points: 2, Visits: 78

Hi ;

When I try to run the package I have this error message

Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Data.SqlClient.SqlException: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Tha hapen on Write execution-end info to exec log table , I found the problem but not solution

the CAst in SQL statment can't CAST the string date format because isn't an ISO format andrstand by SQL server , so the reader in do while loop get an out of range value.

If you have an idea how to fix that please let me know

Regards

Post #953485

Stan Kulp-439977

Posted Friday, July 16, 2010 12:59 PM

SSC Journeyman

Group: General Forum Members
Last Login: Today @ 2:08 PM
Points: 79, Visits: 600

My guess is that it is choking on the elapsed time calculation.

Try commenting out that entire section ('Calculate execution elapsed time..., except for the Dim statement that creates the elapsed time variable) and see if it then works.

If it does, you can probably do without the elapsed time data.

I don't encounter the problem when I run it on my workstation, so I don't really know how to debug it.

If you want to use it, you'll have to play with the code.

Post #954118

mishaluba

Posted Saturday, July 17, 2010 10:36 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 5:54 PM
Points: 525, Visits: 617

Thanks to the author AND to Phil and Bill for the comments and especially the link to Jamie's blog. I found this info invaluable. I have been building SSIS packages for a while, but, I suppose out of habit, have been doing logging sort of "manually" with "Execute SQL" tasks. I will definitely leverage events going forward. Much more efficient and re-usable.

Post #954396

Bill Galashan

Posted Saturday, July 17, 2010 2:39 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 12:33 PM
Points: 124, Visits: 204

From post 2
There are always a million ways to do anything. It's quite possible that your idea is better, but this one works

from post 12
Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Data.SqlClient.SqlException: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

oops doesn't seem to work as claimed in post 2

from post 13
I don't encounter the problem when I run it on my workstation, so I don't really know how to debug it.

no further comment other than than do not re-invent the wheel, the round version we have does a fairly good job.

Post #954440

Stan Kulp-439977

Posted Saturday, July 17, 2010 3:08 PM

SSC Journeyman

Group: General Forum Members
Last Login: Today @ 2:08 PM
Points: 79, Visits: 600

Why don't you do like I did, Bill and write an article and share your knowledge?

Or do you simply prefer telling other people how stupid they are?

Besides which, tell me how to calculate the execution elapsed time of a package WITHOUT doing some "wheel reinvention?"

Is there an event for this?

I know, I know, you're not going to write my code for me, you're just going to tell me I'm stupid.

Post #954445

Bill Galashan

Posted Monday, July 19, 2010 10:56 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 12:33 PM
Points: 124, Visits: 204

I would love the luxury of time to write an article. I work for a very busy online company, and most of the code and utilities I put together actually belong to and are copyright to my employer as this is what they pay me to do.

I have a number of topics that I will, some day, find time to write.

I haven't looked at our SSIS logging in some time as it is there, completely non-intrusive and in fact not even directly visible to a developer. They have to go to the event handlers to see them but from my recollection our logging through the event handlers records the start, end time and duration of each object/element and a running cumulative duration. The only time we need to use this is where we have job failures or performance issues.

Where we have a failure we get a much more meaningful error message than SQL Agent reports and details of the step that actually failed.

Post #954967

BadBoysDriveAudi

Posted Thursday, July 29, 2010 1:08 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, January 30, 2012 7:58 PM
Points: 10, Visits: 37

I think this would be a better solution if the failed container lived in the event handler tab. You could perform the fail email step, then finish logging the the SSIS package execution.

All in all, this solution, as presented, is great.

Post #960968

« Prev Topic | Next Topic »

18 posts, Page 2 of 2

««1 2

Permissions