July 30, 2009 at 3:48 am
I am trying to find a way of generating an alert when an SSIS package fails.
We have a developer who is creating cubes from data sources on different systems. Yesterday, one SSIS package caused the MS DTC to shut down and SQL Server promptly closed down. I want to create an alert when he runs these SSIS packages.
Once I've got that sorted, I would like to understand why SSIS caused this but I'll raise that in a separate topic in the appropriate forum.
:unsure:
Madame Artois
August 17, 2009 at 10:56 am
.
Oraculum
August 17, 2009 at 10:56 am
Are these devlopers just running the SSIS packages from within Visual Studio? in debug mode i guess?? or have they been properly deployed to the server?
Oraculum
August 18, 2009 at 12:44 am
I suspect they are just running them in Visual Studio. Is there any way I can check? Is there an 'idiot's guide to SSIS?
Madame Artois
August 18, 2009 at 6:03 am
To check if the packages are on the server, log into SSMS and open a connection choosing a Server Type of Integration services. Expand Stored Packages and both MSDB (if the package is stored on the sql server) or File System (if the package is stored on the server's file system).
August 18, 2009 at 6:43 am
ok nothing I can think of to monitor those crafty developers!
....One thing you may be able to do if you know the host running the package is to have a job that looks through some of the DMV's in 2005 for a specific sql text or host name. This maybe a bit to much of an overhead but you could set the time of the job to 5 mins (depending on how long the OLAP process packages take to complete) and you might pick them up!
You could have a look at the below article, maybe alter the stored procedure script to pick up certain host names of the developers PC's. When one is found then email you the details.
http://www.sqlservercentral.com/articles/DMV/64425/
** Failures in packages sorry should be handled using the Event Handlers section in SSIS. This enables you to create on event actions and logs to help on error or failure.. sorry think i misread you initial post.
http://msdn.microsoft.com/en-us/library/ms140011.aspx
and
and logging
http://msdn.microsoft.com/en-us/library/ms138020.aspx
Oraculum
August 18, 2009 at 7:00 am
They are stored in the File System folder
Madame Artois
August 18, 2009 at 7:06 am
Can you remove their access from the server and set the package up to run as a job?
August 18, 2009 at 7:26 am
I don't know how to remove their access from the server but, at the minute, the developer is still 'developing' them. I was orginally wondering if an alert could be triggered by one of these packages running.
Madame Artois
August 18, 2009 at 7:36 am
Yes it can but the developer needs to add this in as i mentioned earlier..
... using the Event Handlers section in SSIS. This enables you to create on event actions and logs to help on error, failure, completion..
http://msdn.microsoft.com/en-us/library/ms140011.aspx
and
and logging
http://msdn.microsoft.com/en-us/library/ms138020.aspx
Oraculum
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply