Monitoring the running and execution of SSIS packages with MOM

Hi,

Has anyone monitored the execution of SSIS packages with MOM? Are there extreme benefits over just utilizing the built in execution and event logs, as well as the Windows Event Viewer?

What is the recommended way to monitor SSIS execution?

Thanks,

- Joel

Joel,

I have a very very limited knwoledge of MOM... However you can log many things to the event log with SSIS (job execution with SQL Agent, logging to event log, ...). I'm not aware of anything "standard" for SSIS & MOM...

|||

They're not quite there yet with MOM integration. We had a similar project to integrate into Tivoli and the way I got around it was to use logging providers to output into the Windows Event Log. Tivoli had hooks into the Event Log for the events I gave it and whalla....pagers blaring. If your concern is more from the operations perpective, you may have better luck using the SQL Server log provider and creating operations reports for your production support. You can use these and events like the OnPipelineRowsSent to trap "hung" packages or tasks.

Sorry there's not a better answer but direct MOM integration is just a matter of time.

-- Brian

sql

MOM blocking analysis

MOM contains its own Block Analysis script. It is a script written on
VB, that looks for the monitored servers, creates for each one
MomCreateObject("SQLDMO.SQLServer"), afterwards connects to each
database on each server and executes SELECT GETDATE() query. If MOM
doesn't receive answer for 6 minutes, it sends alert. Several times
since I started to monitor my servers I received the next alert:

The program "SQLDMO_789" has been blocked for 6 minutes on database
BurstingDataWarehouse in the SQL instance MSSQLSERVER. The defined
acceptable blocking threshold is 1 minute(s). "SQLDMO_789" is running
on SPID 134 as login NT AUTHORITY\SYSTEM and is blocked by SPID 133.
The resource id is KEY: 10:2:1 (a2007950f190)

SQLDMO_789 - is the MOM itself (number varies from time to time). 10 -
is BurstingDataWarehouse database. As far as I can judge, MOM connects
to the server succesfully (otherwise, how can it know SPID?) The server
itself worked fine at that time - nothing unusual, all the jobs
finished succesfully including the heavy ones. What can block SELECT
GETDATE() query for 6 minutes?I had a similar issue and it turned out that some scrappy application
held thousands of locks on the tempdb. Run sp_who2 in combination with
sp_lock to see which process is blocking the MOM agent.

M