SSIS Job Statistics
I am trying to find out SSIS Job Statistics Like
Jobs that are running per account (so how many and which jobs run from which system accounts vs which user accounts)
If I am calling a SSIS package from SSISDB Catalog, then I should know which account is calling that package. IS it a SQL Server Agent account or any other user Account.
Is there any way to get this info?
One Solution collect form web for “SSIS Job Statistics”
SSIS has some internal reports you could use. Below the Integration Services tab, you can right click on SSISDB and go to
Reports > All Executions. This will give you all current and recent executions. If you click on
Overview for a given execution, there is a field called
CALLER_INFO which will tell you if this was called from SQL Agent. Note that if you are not an admin, certain executions may be filtered from your view in the report.
If you have permissions to query the SSISDB database directly, you can use the following which will give you the account name that called the execution as well as the account that is running the current operation:
select execution_id , folder_name , project_name , package_name , environment_name , executed_as_name , start_time , end_time , CASE status WHEN 1 THEN 'Created' WHEN 2 THEN 'Running' WHEN 3 THEN 'Cancelled' WHEN 4 THEN 'Failed' WHEN 5 THEN 'Pending' WHEN 6 THEN 'Ended Unexpectedly' WHEN 7 THEN 'Succeeded' WHEN 8 THEN 'Stopping' ELSE 'Completed' END AS Status , caller_name , process_id , server_name from internal.execution_info --Optionally filter just running jobs WHERE status = 2
This is a view which has the following filter:
WHERE opers.[operation_id] in (SELECT id FROM [internal].[current_user_readable_operations]) OR (IS_MEMBER('ssis_admin') = 1) OR (IS_SRVROLEMEMBER('sysadmin') = 1)
So if you are not an admin, the results will be filtered to those executions that you have permission to see. You could also query the internal tables directly to work around this limitation.
In the case of SQL Agent, you could use a query like below, which was adapted from this: https://www.mssqltips.com/sqlservertip/2561/querying-sql-server-agent-job-information/
The step will either be running under the database user name, proxy or the owner of the job, depending on how it’s configured.
SELECT [sJOB].[job_id] AS [JobID] , [sJOB].[name] AS [JobName] , [sDBP].[name] AS [JobOwner] , [sCAT].[name] AS [JobCategory] , [sJOB].[description] AS [JobDescription] , CASE [sJOB].[enabled] WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END AS [IsEnabled] , [sJOB].[date_created] AS [JobCreatedOn] , [sJOB].[date_modified] AS [JobLastModifiedOn] , [sSVR].[name] AS [OriginatingServerName] , [sJSTP].[step_id] AS [JobStartStepNo] , [sJSTP].[step_name] AS [JobStartStepName] , CASE WHEN [sSCH].[schedule_uid] IS NULL THEN 'No' ELSE 'Yes' END AS [IsScheduled] , [sSCH].[schedule_uid] AS [JobScheduleID] , [sSCH].[name] AS [JobScheduleName] , CASE [sJOB].[delete_level] WHEN 0 THEN 'Never' WHEN 1 THEN 'On Success' WHEN 2 THEN 'On Failure' WHEN 3 THEN 'On Completion' END AS [JobDeletionCriterion] , proxyName.name , sJSTP.database_user_name FROM [msdb].[dbo].[sysjobs] AS [sJOB] LEFT JOIN [msdb].[sys].[servers] AS [sSVR] ON [sJOB].[originating_server_id] = [sSVR].[server_id] LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT] ON [sJOB].[category_id] = [sCAT].[category_id] LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sJSTP] ON [sJOB].[job_id] = [sJSTP].[job_id] AND [sJOB].[start_step_id] = [sJSTP].[step_id] LEFT JOIN msdb.dbo.sysproxies proxy ON sJSTP.proxy_id = proxy.proxy_id LEFT JOIN [msdb].[sys].[database_principals] proxyName ON proxyName.sid = proxy.user_sid LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP] ON [sJOB].[owner_sid] = [sDBP].[sid] LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH] ON [sJOB].[job_id] = [sJOBSCH].[job_id] LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH] ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id] ORDER BY [JobName]