Copy-and-Paste SSIS Execution Monitoring with Failure-Alert Email
-
Comments posted to this topic are about the item Copy-and-Paste SSIS Execution Monitoring with Failure-Alert Email
-
Anything wrong with the event handlers already available within SSIS. It is very simple to set up an SSIS template with the event handlers coded to generate logging, thereafter use this template as the basis for all new SSIS packages and a developer can then ignore logging as it is all built ready to go.
-
You could make a template of this package, too, and build all your SSIS packages by adding components to the sequence container.
There are always a million ways to do anything. It's quite possible that your idea is better, but this one works. Any logging is far better than no logging. I pretty much always know what is going on with our jobs. I know when a failure occurs before the operators do.
-
I do something similar, mainly because I find using SQL to select the interesting parts of my log more natural than processing an unstructured text file.
I have a ToLOG(Level, Message) procedure that gets called from my code at the end of each step - and if I'm debugging new code, during it.
It records the SSIS run start time, current time, SSIS task name, Level and Message. The start time means I can easily display results for last run only, and calculate duration from start if checking where the delays are. Level is 0 for Debug, 1 for normal messages, 2 for data load complete, up to 8 for Fatal Error.
One problem with using ToLOG() is that it only logs events in script components. So I also have an SQL version for use in the SQL procedures I use in Execute SQL tasks.
Then as the final task I mail the log content (WHERE Level>0 to hide Debug messages) to myself and my customer. I turn the SQL result into an HTML table, with Completed steps flagged in green and Errors in red, which makes it easy for my customer to see what happened - important as I work for them part-time and they need to be able to fix simple errors in the input data files (the usual cause of an error) themselves, and only call me for real problems.
-
Thanks for the article.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Bill Galashan (7/12/2010)
Anything wrong with the event handlers already available within SSIS. It is very simple to set up an SSIS template with the event handlers coded to generate logging, thereafter use this template as the basis for all new SSIS packages and a developer can then ignore logging as it is all built ready to go.Good article, but I'd have to go with Bill on this one and say why not use event handlers. They provide so much more in terms of logging functionality.
The fact that you've gone to such lengths to create this logging functionality seems to indicate that you are not fully aware of the features available in the product you're using. Maybe you need to do a bit more research and planning before diving into the development end. Probably could have saved yourself some development time and produced a much more robust, adaptable system.
Take a quick look over this article Custom Logging Using Event Handlers by SSIS Guru Jamie Thompson, for quick look at the functionality.
--------------------
Colt 45 - the original point and click interface
-
Is there an event handler to get the time stamp of a source data file being extracted by an SSIS package?
-
Stan Kulp-439977 (7/13/2010)
Is there an event handler to get the time stamp of a source data file being extracted by an SSIS package?Sorry, but I'm not going to provide the coding for you. The article referenced in my earlier post clearly shows how you can put your own code into the event handling.
--------------------
Colt 45 - the original point and click interface -
Sorry, but the answer is you can't get the time stamp of a processed data file from an event handler.
I was just trying to show you why I resorted to what I did.
You cannot recreate the functionality of my code with event handlers alone.
-
Stan Kulp-439977 (7/14/2010)
Sorry, but the answer is you can't get the time stamp of a processed data file from an event handler.I was just trying to show you why I resorted to what I did.
You cannot recreate the functionality of my code with event handlers alone.
Sorry, but you CAN.
All the components that are available in the control flow are available in an event handler. So how about you put the VB.Net code you've written into a script component on the Event handler tab? Maybe even choose the OnPreValidate or OnPreExecute events so you log the file time stamp before beginning to load the file.
And as Bill pointed out earlier, once this all setup in a template package, subsequent packages based on the template come complete with logging already configured.
--------------------
Colt 45 - the original point and click interface -
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.
-
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
-
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.
-
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.
-
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.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply