How to find execution Status of a package

• sql_usr

  Mr or Mrs. 500

  Points: 500

  More actions

  February 17, 2010 at 8:11 am

  #216833

  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?

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  February 17, 2010 at 11:45 am

  #1120320

  Are you running from a job or from SSIS?

  I have not had any problem identifying which process is running a Package but perhaps I'm missing something.

  I first identify the SPID, then identify what the Process is doing.

  I like to use Logs to record each step and record the status, including and error codes and massages so that I can identify what went wrong.

  You can write to a text file or you can create a table and write a script to purge records.

  There are many ways to do this...

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/