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 Wednesday, July 14, 2010 1:32 PM
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
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
Posted Thursday, July 15, 2010 2:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 8:24 AM
Points: 2, Visits: 84
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
Posted Friday, July 16, 2010 12:59 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
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
Posted Saturday, July 17, 2010 10:36 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 11:29 AM
Points: 536, Visits: 764
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
Posted Saturday, July 17, 2010 2:39 PM
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
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
Posted Saturday, July 17, 2010 3:08 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
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
Posted Monday, July 19, 2010 10:56 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
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
Posted Thursday, July 29, 2010 1:08 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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 »

Add to briefcase ««12

Permissions Expand / Collapse