Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  SSIS and Import/Export (2005)  How to find execution Status of a running package

Author  Topic  sql_usr Starting Member 1 Post Posted - 2010-02-17 : 10:22:28 Lets say I have Package A, Package B, Package C running. I want to have a 'health monitoring' package that checks that A,B, and C are actually running . That they have not encountered error and shut down. At the start of the run time of Package A,B,and C, I update a SQL Server table that lists the package name and its status. RunTable PckgName IsRunning Package A ---> 1 Package B ---> 1 Package C ---> 1 The health monitoring package will run concurrently w/ Package A,B,C and if IsRunning is set to '1', will check if the package is actually running. If the package has shut down, It'll send an email to its operator. All the individual packages have their own Error Handlers but we are still building this Health Monitoring Package just in case. What I can't figure out is how to actually check if a specific .dtsx package is running. I tried using sys.dm_exec_sessions and sys.dm_exec_query_stats but the package name does not show up under the PROGRAM_NAME column when you run sys.dm_exec_sessions. I tried utilizing WQL by running WMI Event Watcher task but I don't know what the WQL query syntax will be (How to utilize __InstanceModificationEvent class). Any suggestions?