Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Copy-and-Paste SSIS Execution Monitoring with Failure-Alert Email Expand / Collapse
Author
Message
Posted Saturday, July 10, 2010 3:04 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 12, 2014 1:36 PM
Points: 128, Visits: 916
Comments posted to this topic are about the item Copy-and-Paste SSIS Execution Monitoring with Failure-Alert Email
Post #950388
Posted Monday, July 12, 2010 12:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, June 8, 2014 11:23 PM
Points: 129, Visits: 216
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.
Post #950585
Posted Monday, July 12, 2010 7:01 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 12, 2014 1:36 PM
Points: 128, Visits: 916
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.
Post #950717
Posted Monday, July 12, 2010 9:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 11, 2014 6:37 PM
Points: 115, Visits: 746
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.
Post #950830
Posted Monday, July 12, 2010 4:35 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 9:09 PM
Points: 17,628, Visits: 15,487
Thanks for the article.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #951098
Posted Tuesday, July 13, 2010 4:17 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 4:59 PM
Points: 2,693, Visits: 1,204
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.



Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
Post #951269
Posted Tuesday, July 13, 2010 10:09 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 12, 2014 1:36 PM
Points: 128, Visits: 916
Is there an event handler to get the time stamp of a source data file being extracted by an SSIS package?
Post #951988
Posted Tuesday, July 13, 2010 11:12 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 4:59 PM
Points: 2,693, Visits: 1,204
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.



Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
Post #952015
Posted Wednesday, July 14, 2010 6:44 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 12, 2014 1:36 PM
Points: 128, Visits: 916
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.
Post #952275
Posted Wednesday, July 14, 2010 7:51 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 4:59 PM
Points: 2,693, Visits: 1,204
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.



Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
Post #952352
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse