Method 1 : YOu can use the below VB code in SQL agent job to find the information on packages running on sql server 2012:
'...
' Declare and instantiate objects here.
Dim app As New Application
'...
' Create a RunningPackages collection, named pkgs, and fill it
' with the running packages from the application object.
Dim pkgs As RunningPackages = app.GetRunningPackages(Nothing)
' Enumerate over each package in the collection and display some data.
For Each package As RunningPackage In pkgs
Console.WriteLine("InstanceID: " & package.InstanceID.ToString())
Console.WriteLine("PackageDescription: " & package.PackageDescription)
Console.WriteLine("PackageID: " & package.PackageID.ToString())
Console.WriteLine("PackageName: " & package.PackageName)
Console.WriteLine("UserName: " & package.UserName)
Next
' Insert more code here.
Method 2: Refer to below excellent read from Jamie Thomson which have neat and clean scripts to provide info on you're running packages:
http://sqlblog.com/blogs/jamie_thomson/archive/2009/10/18/collecting-information-about-your-ssis-packages-ssis-nugget.aspx
Method 3: YOu can use below SQL code which i've been using:
SET NOCOUNT ON
-- Check if the SQL Server Agent is running
IF EXISTS ( SELECT 1
FROM MASTER.dbo.sysprocesses
WHERE program_name = N'SQLAgent - Generic Refresher' )
BEGIN
SELECT @@SERVERNAME AS 'InstanceName' ,
1 AS 'SQLServerAgentRunning'
END
ELSE
BEGIN
SELECT @@SERVERNAME AS 'InstanceName' ,
0 AS 'SQLServerAgentRunning'
RAISERROR('The SQL Server Agent is not running.', 16, 1) WITH SETERROR ;
END
-- Execute the script
IF EXISTS ( SELECT *
FROM tempdb.dbo.sysobjects
WHERE id = OBJECT_ID(N'[tempdb].[dbo].[Temp1]') )
DROP TABLE [tempdb].[dbo].[Temp1]
GO
CREATE TABLE [tempdb].[dbo].[Temp1]
(
job_id UNIQUEIDENTIFIER NOT NULL ,
last_run_date NVARCHAR(20) NOT NULL ,
last_run_time NVARCHAR(20) NOT NULL ,
next_run_date NVARCHAR(20) NOT NULL ,
next_run_time NVARCHAR(20) NOT NULL ,
next_run_schedule_id INT NOT NULL ,
requested_to_run INT NOT NULL ,
request_source INT NOT NULL ,
request_source_id SYSNAME COLLATE database_default
NULL ,
running INT NOT NULL ,
current_step INT NOT NULL ,
current_retry_attempt INT NOT NULL ,
job_state INT NOT NULL
)
DECLARE @job_owner SYSNAME
DECLARE @is_sysadmin INT
SET @is_sysadmin = ISNULL(IS_SRVROLEMEMBER('sysadmin'), 0)
SET @job_owner = SUSER_SNAME()
INSERT INTO [tempdb].[dbo].[Temp1]
EXECUTE MASTER.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
UPDATE [tempdb].[dbo].[Temp1]
SET last_run_time = RIGHT('000000' + last_run_time, 6) ,
next_run_time = RIGHT('000000' + next_run_time, 6) ;
-----
SELECT j.name AS JobName ,
j.enabled AS Enabled ,
CASE x.running
WHEN 1 THEN 'Running'
ELSE CASE h.run_status
WHEN 2 THEN 'Inactive'
WHEN 4 THEN 'Inactive'
ELSE 'Completed'
END
END AS CurrentStatus ,
COALESCE(x.current_step, 0) AS CurrentStepNbr ,
CASE x.running
WHEN 1 THEN js.step_name
ELSE NULL
END AS CurrentStepName ,
CASE WHEN x.last_run_date > 0
THEN CONVERT (DATETIME, SUBSTRING(x.last_run_date, 1, 4) + '-'
+ SUBSTRING(x.last_run_date, 5, 2) + '-'
+ SUBSTRING(x.last_run_date, 7, 2) + ' '
+ SUBSTRING(x.last_run_time, 1, 2) + ':'
+ SUBSTRING(x.last_run_time, 3, 2) + ':'
+ SUBSTRING(x.last_run_time, 5, 2) + '.000', 121)
ELSE NULL
END AS LastRunTime ,
CASE h.run_status
WHEN 0 THEN 'Fail'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancel'
WHEN 4 THEN 'In progress'
END AS LastRunOutcome ,
CASE WHEN h.run_duration > 0
THEN ( h.run_duration / 1000000 ) * ( 3600 * 24 )
+ ( h.run_duration / 10000 % 100 ) * 3600 + ( h.run_duration
/ 100 % 100 )
* 60 + ( h.run_duration % 100 )
ELSE NULL
END AS LastRunDuration ,
js.command AS SSISPackageExecutionCommand
FROM [tempdb].[dbo].[Temp1] x
LEFT JOIN msdb.dbo.sysjobs j ON x.job_id = j.job_id
JOIN msdb.dbo.sysjobsteps js ON js.job_id = j.job_id
LEFT OUTER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
LEFT OUTER JOIN msdb.dbo.sysjobhistory h ON x.job_id = h.job_id
AND x.last_run_date = h.run_date
AND x.last_run_time = h.run_time
AND h.step_id = 0
WHERE x.running = 1
AND js.subsystem = 'SSIS'
DROP TABLE [tempdb].[dbo].[Temp1]
